Two conditions with one formula

[tryingtoexcelatexcel]

Hi Excel Experts,

So I have a table that ranks expenditures from largest to smallest and then I created another table using index/match to get the table into the correct order.

But for 2 items, "other operating expenses" and "staff costs", I need to remove/add additional numbers to them for reporting purposes.

I'm not sure if I am explaining this very clearly so might be better to see the excel sheet.

But the goal is I need to remove "costs of unmaterialized osaka seats" from other operating expenses which I have done with the following formula:

=IF($I15="Other operating expenses",INDEX($C$4:$F$22,MATCH($H15,$B$4:$B$22,0),MATCH(J$3,$C$3:$F$3,0))-J$18,INDEX($C$4:$F$22,MATCH($H15,$B$4:$B$22,0),MATCH(J$3,$C$3:$F$3,0)))

but I also would like the table to be able to automatically add "crew allowances" to "staff costs", right now I am manually adding in crew allowances every month but the ranking of expenditures will change each month and if I forget to fix the cells manually then next month "crew allowances" may get added to the wrong expenditure and not "staff costs"

i.e.
=IF($I7="Other operating expenses",INDEX($C$4:$F$22,MATCH($H7,$B$4:$B$22,0),MATCH(J$3,$C$3:$F$3,0))-J$18,INDEX($C$4:$F$22,MATCH($H7,$B$4:$B$22,0),MATCH(J$3,$C$3:$F$3,0)))+D4


Crew Allowance Staff Costs.xlsx

I think the excel sheet can explain a bit better the problems I am facing.

Thank you very much!

Joseph

[José Augusto]

Hi

I include a new column B with this formula

=A4-(A4>$A$4)-A4*(A4=$A$4)

Please Login or Register  to view this content.

I change yor formula to incorporate your two conditions
=INDEX($C$4:$F$22,MATCH($H4,$A$4:$A$22,0),MATCH(P$3,$C$3:$F$3,0))-P$18*($I4="Other operating expenses")+D$4*($O4="Staff costs")

Please Login or Register  to view this content.

you need enter data to "Cost of Unmaterialized Osaka Seats"

See the file Crew Allowance Staff Costs.xlsx where I hid your output table and put a new table

Regards

[FDibbins]

Here is a formula that will make your 2nd table without helpers....
G4=IFERROR(INDEX(C$4:C$22,MATCH(LARGE($E$4:$E$22,ROWS($1:1)),$E$4:$E$22,0)),"")
copied down
Then for the values...
=INDEX($C$4:$F$22,MATCH($I4,$C$4:$C$22,0),MATCH(J$3,$C$3:$F$3,0))-IF($I4="Other operating expenses",-INDEX($C$4:$F$22,MATCH("Other operating expenses",$C$4:$C$22,0),MATCH(J$3,$C$3:$F$3,0)),0)
This eliminated the need to ID which row "Others" is in.

You can use the same approach for the other additions/subtractions

[tryingtoexcelatexcel]

Oh ok thank you both. I think I need some time to digest these solutions.

For Jose's solution can you help explain how P$18*($I13="Other operating expenses") work? I'm guessing it subtracts p18 when i9 finds "other operating expenses" but I've never seen a formula done this way so I'm not too familiar with it.

For FDibbins's solution I wasn't sure why ROWS ($1:1) was applied to the first formula. For the second formula is there anyway to combine the addition of "crew costs" and removal of "cost of osaka seats" in the same formula?

[José Augusto]

Hi

If ($I13="Other operating expenses") is true then evaluate as 1, else evaluate as 0. So P$18 is multiplied by 1 if ($I13="Other operating expenses") is true or zero if ($I13="Other operating expenses") is false

Regards

[tryingtoexcelatexcel]

Hi Jose,

Sorry for the very late reply. I have been on holiday and havent been looking at work related material lately. But thank you very much for the response. Is this an if statement:

($I4="Other operating expenses")

so without writing if($I4=..., then if yes..., then if no...) I can shorten the formula to just be ($I4="Other operating expenses")?

Thank again and happy holidays!

[FDibbins]

in a LARGE() - or SMALL() - the syntax is =LARGE(range,position), so by using ROWS($1:1), I can increase the position number automatically as it gets copied down, no need for manual adjustment
MATCH(LARGE($E$4:$E$22,ROWS($1:1))...ROWS($1:1) would give 1
next row down would give...
MATCH(LARGE($E$4:$E$22,ROWS($1:1))...ROWS($1:2) would give 2
MATCH(LARGE($E$4:$E$22,ROWS($1:1))...ROWS($1:3) would give 3
etc

I will take a look at the other part