Search results

Hi Belleke, I drafted a solution that appears to be quite similar to Bosco's (well I mutliplied by 1000 rather than 0.5, but otherwise it is very close), except that I made a table in your "Reservaties" sheet, because it makes the formula more readable, and it is more flexible, as the table...

In the sub-formula below: Invoiced will be added if none of them is 0 => (Table1[INVOICED]*(Table1[NET]<>0)*(Table1[REV AMT]<>0)) the blue amount will be summed only when the purple amount is not 0 (or empty), and when the green amount is not 0 or empty. This is because the (Table1[NET]<>0)...

Well, I would have said "yes, using data > text to column, and choosing space and S as delimiters". However, it seems that you have several cells which contain newline characters (\n\r aka crlf aka chr(13)chr(10)). Except if someone knows a way to remove them with search and replace, I would go...

See attached example. Sumproduct, but with no array formula (and no added column). I changed your Sheet2 range to Table for flexibility. Be careful that in your sheet2, sometimes there is a net and a invoiced, but no rev amt. In this case nothing is added, as I exactly applied your request...

Not sure if I properly get what you are trying to achieve, but if you want to replace all "Free Standing" strings with "MySheets" strings in your formula, I would select all the cells where this formula appears, and CTRL+H (kb shortcut for "search and replace"), then type Free Standing in the...

Probably the best solution (unless someone else finds a flaw): No array formula, no delimiter, no cell type dependency.

There are pros and cons to each solution: Index and Match solution is more flexible, because it will work regardless of the type of data in column C. Sumifs will only work if column C has numeric values, but it does not need the CSE (ctrl+shift+enter) to make it an array formula, which is more...

Hi Chihiro, Thank you for your response. FY18Q4 should not be filtered out. That's just a mistake. And the reason why I sum backward is because I have ongoing deals as of today. They have an expected completion date in the future. As I am in FY17 Q2, I want to have the volume of what is ahead...

Hi Chihiro, I receive the data this way (with more columns indeed), except the calculated columns (margin %), and the fiscal quarter which is actually a lookup on a dimdate table where the mapping is done, based on an end date in the raw data. Anyway, let's assume the raw data are only: ID, A...

I would take PCosta formula and change it to: =INDEX($C$2:$C$89,MATCH(F2&"-"&G2,$A$2:$A$89&"-"&$B$2:$B$89,0)), and still apply it as array formula (Ctrl-Shift-Enter). Reason is because if you have, for example, 3 in VT and 21 in CT , and elsewhere 32 in VT and 1 in CT, both will match PCosta's...

Well, my browser was was causing the issue with the file type. I took another browser, and now it's ok. Please see attached.

Hello, I am trying to show forecasts with cumulated data. My data table is as follows: ID - unique identifier A Revenue - number or "-" if empty A Margin - number or "-" if empty A Margin% - formula: =iferror([A Margin]/[A Revenue],"-") B Revenue - number or "-" if empty B...

I noted in your destination worksheet, that you have some formulas in column L. Please find below some changes to cope with that too. First of all, I recommend that you delete the extra rows on worksheet "both". We'll take care of them in the listrow object in vba code. Although in this...

From file extension I guess your XL version >= 2007. If yes, how about making your "both" data sheet a table (through insert > table). The code below assumes your range selection macro works (I did not give it a try), and both source (Book1.xlsx) and destination (data.xlsm) workbooks are opened...

Hi Peter, Sorry for the late answer, I got preempted on something (more) urgent. Thank you for your response. While your solution also satisfies my need, I finally managed to solve the issue by adding a (boolean) calculated column in Powerpivot, which is easier to maintain than VBA code. I...

Hi Peter, I'd almost have preferred to read that I'm dumb today ;) I can live with VBA if there is no other option. Otherwise if powerpivot offers a solution, that could be even better (I'm on 2013, and already use powerpivot for calculating moving averages and so on).

Hello, I don't know if it's because it's Monday, but I can't find a way to do something which I feel should be very basic. I have a data table (containing survey results) which I base many pivots on. In one of the pivots, I need to compute / calculate averages or counts of responses only when...

Hi, I'm using powerpivot to process survey data. I created a date dimension table, that is referenced by other tables containing dates. It looks like this: DateKey MonthNumber MonthName Fiscal Quarter Calendar Year Fiscal Year 4/30/2013 4 Apr Q2...

[Edit] That comment is related to Luke's post, not to Hui's [Edit] That works if ashish uses a new range of cells to harvest the quoted values. But what if he wants to modify the contents of the current numeric cells to quote them? As far as I know, Excel does not offer regular...

Luke, thank you for your answer. They are always fast and efficient. blockquote If there's no overlap, why not just use the shared file as is? There should be no need to make a copy. /blockquote Because the file is archived on sharepoint, and both my teammate and I picked version x.0 but...

I'm working on a shared file, and one of my teammate needs also to work on the same file simultaneously. I know we are not performing changes to the same tabs in the file, so there will be no overlap. So I chose to work on a copy of the file, so as to keep progressing. Now, when she will...

Yes I found it strange as well. I saw no reason why this should fail for that specific quarter. Then digging into the results I expected vs. the results I got, I finally pinpointed it. If you remember, I cannot rely on my raw Qtr data, because sometimes the Qtr data does not reflect the actual...

Thank you Luke. It works fine, except for my last quarter, where it seems many projects are not counted. I'm trying to see which are selected (or which ones are left aside). But all in all it satisfies my needs. Thanks again. Catherine

Here are some additional details: I have one macro-enabled workbook with raw data (in which I have some UDF running to populate calculated fields for easier data crunching). I have a second workbook in which I have some slicers for the users to filter their data set, and present the resulting...

Thanks for your answer Luke. Data I use are raw data, and I'd rather not put an ordering constraint on that source, given that I perform many other calculations. What if my other calculations need as well to rely on a specific (and distinct) ordering? Moreover, I would need several additional...