Calculating values from different input files and forming a table from it

[gavinloi]

Hi all,

I am stuck with an issue that I have 5 different input files that I am required to get the result table below.

(1) I am trying to use a union to get the difference between receipts A and B

(2) After that I am trying to get the table below. The data I have would be Food (2), Drinks (3) and Excess (13). I would be able to compute the total and difference in the same column but the "After Excess" column I am stuck. In excel, the formula would be

IF(Food > Drinks, Food + Difference, Food) and vice versa for Drinks. The last column "Difference" would tie back to the figure in B8.

Would this even be possible or is there a direction that i can look towards. Any help would be much appreciated.

Thank you!

[ArtApa]

Hi @gavinloi - Where did you get After Excess (2, 11) values from? They are not in your workflow.

[gavinloi]

Hi @ArtApa, the After Excess (2, 11) values would be need to be calculated. For the value "2" if value 2 is greater than 3 in column B then value 2 if not difference of 13-3=10 the formula in excel "IF(Food > Drinks, Food + Difference, Food)". The same goes for value 11, if value 3 greater than 2 in column B then value would be the difference of 13-2= 11 the formula in excel "IF( Drinks>Food, Drinks+ Difference, Drinks)". Hope this clarifies.

[ArtApa]

Hi @gavinloi - Not at all. Something is wrong here.

Food = 2

Drinks = 3

Difference (receipt A – Receipt B) = 0

Excess = 13

"IF(Food > Drinks, Food + Difference, Food)". If 2>3 then 2+0 else 2

Result: 2

"IF( Drinks>Food, Drinks+ Difference, Drinks)". IF 3>2 then 3+0 else 3

Result: 3 (Not 11)

If you can post your Excel with formulas here, that will help. 

[gavinloi]

Hi @ArtApa, the excess would be more of a separate figure and it comes from another source file which needs to be included in the food or drinks. hence the column "after excess". I have attached the excel with the formulas for easier reference, hope that this helps. Thank you

[ArtApa · Accepted solution]

Hi @gavinloi - Please have a look.

[gavinloi]

Many thanks @ArtApa , this works well! Thank you so so much!