Help please using excel 2007
I am trying to set up a formula that will calculate a value based on multiple criteria.
So far I have set up 4 separate "if" statements but I need to combine them into one that will provide an amount based on conditions. Each individual if statement works ok and calculates correctly. If anyone can help I would be extremely grateful.
=IF(AND(A37="Unit T",C37="Repair",OR(D37="Hoist",D37="shower",D37="bath",D37="recliner",D37="Pillow",D37="cushion",D37="Lift",D37="Station")),G37*40.96+J37*40.96+N37+O37*0.5,"")
=IF(AND(A38="Unit T",C38="call-out",OR(D38="Hoist",D38="shower",D38="bath",D38="recliner",D38="Pillow",D38="cushion",D38="Lift",D38="Station")),G38*61.01+J38*61.01,"")
=IF(AND(A39="Unit T",C39="Repair",OR(D39="Bed",D39="Mattress")),G39*61.01+J39*61.01,"")
=IF(AND(A40="Unit T",C40="call-out",OR(D40="Bed",D40="Mattress")),G40*80.7+J40*80.7,"")
In summary there are 4 different charge rates depending on whether it is a repair or call out and the type of call out e.g. pillow etc.
Is there a way of combining all of the above into one workable if statement?
Thanks in advance
Hi Jan,
I think a VLOOKUP or INDEX & MATCH formula would be better. This isn't a job for IF.
With VLOOKUP or INDEX & MATCH you can find the correct charge out rate based on your criteria from a table that contains the rules/rates.
VLOOKUP tutorial: https://www.myonlinetraininghub.com/excel-2007-%e2%80%93-vlookup-formulas-explained
INDEX & MATCH tutorial: https://www.myonlinetraininghub.com/excel-index-and-match-functions
If you need further help please provide a sample Excel file with examples of the data and the desired results, and preferably showing your attempt so we can help you figure out where you went wrong.
Mynda
