How to add another calculation to an If statement and Index/Match

Hi,
Hopefully this will be an easy one. My brain is going completely blank on this.
I have a formula that is basically calculating/selecting one of three values based on which is not empty. It is based on the user checking the "Included" box to trigger an index/match formula to show the cost (Hourly, ,Unit or Fixed), and then the user inputs the number of hours, number of units or nothing if it is a fixed cost. Then the Estimated Costs field will show which of the costs are populated.
I need to add a "% markup" field that will either do one of the following (I am not sure which one yet so I'm asking for help on both formulas)
I would like to multiply the value the user inputs in the "% Markup" field by the "Estimated Cost" and use that as the "Estimated Cost" total if there is a value in the "% Markup" field. I know I can use a helper field but I'd rather not if I don't have to. So hopefully it will work.
OR
I would like to multiply the value the user inputs in the "% Markup" field by the "Hourly Cost" or the "Unit Cost" or the "Fixed Cost" if there is a value in the "% Markup" field. I think too many "OR"s will make this way too cumbersome so if I have to add additional "% Markup" fields (one for each of the costs) I am willing to do that. But I'm not sure how to do the multiplication using the Index/Match formula.
Here is a mockup of the table so you can see what I have to start:
Included | Project ID | Project | Category | Hours | Hourly Cost | Units | Unit Cost | Fixed Cost | % Markup | Estimated Costs |
---|---|---|---|---|---|---|---|---|
x | Plans & Specifications | 10 | $ 150.00 |
|
|
|
| $ 1,500.00 |
| Plan Review |
|
|
|
|
|
|
|
x | Permits |
|
|
|
| $ 2,000.00 |
| $ 2,000.00 |
x | Survey |
|
| 1 | $ 300.00 |
|
| $ 300.00 |
My formulas are as follows:
Hourly Cost:
=IF(Included@row = false, "", INDEX({Hourly Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0)))
Unit Cost:
=IF(Included@row = false, "", INDEX({Unit Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0)))
Fixed Cost:
=IF(Included@row = false, "", INDEX({Fixed Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0)))
Estimated Costs:
=IFERROR(IF(Hours@row <> "", Hours@row * [Hourly Cost]@row, IF(Units@row <> "", Units@row * [Unit Cost]@row, [Fixed Cost]@row)), "")
I hope this makes sense. Thank you in advance!
Lisa
Best Answer
-
Hi Lisa,
Try the below, personally I think scenario 1 below is cleaner and easier to understand for a viewer other than yourself.
Scenario 1 - Total Estimated Costs:
=IFERROR(IF(Hours@row <> "", Hours@row * [Hourly Cost]@row, IF(Units@row <> "", Units@row * [Unit Cost]@row, [Fixed Cost]@row)) + IF(Hours@row <> "", Hours@row * [Hourly Cost]@row, IF(Units@row <> "", Units@row * [Unit Cost]@row, [Fixed Cost]@row)) * [% Markup]@row, "")
Scenario 2 - % Markup applied to each cost type:
Hourly Cost:
=IF(Included@row = false, "", INDEX({Hourly Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Hourly Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row
Unit Cost:
=IF(Included@row = false, "", INDEX({Unit Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Unit Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row
Fixed Cost:
=IF(Included@row = false, "", INDEX({Fixed Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Fixed Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row
Many thanks,
Adam
Adam Collins
Sr Clinical Development Operations Analyst
Syneos Health
Answers
-
Hi Lisa,
Try the below, personally I think scenario 1 below is cleaner and easier to understand for a viewer other than yourself.
Scenario 1 - Total Estimated Costs:
=IFERROR(IF(Hours@row <> "", Hours@row * [Hourly Cost]@row, IF(Units@row <> "", Units@row * [Unit Cost]@row, [Fixed Cost]@row)) + IF(Hours@row <> "", Hours@row * [Hourly Cost]@row, IF(Units@row <> "", Units@row * [Unit Cost]@row, [Fixed Cost]@row)) * [% Markup]@row, "")
Scenario 2 - % Markup applied to each cost type:
Hourly Cost:
=IF(Included@row = false, "", INDEX({Hourly Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Hourly Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row
Unit Cost:
=IF(Included@row = false, "", INDEX({Unit Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Unit Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row
Fixed Cost:
=IF(Included@row = false, "", INDEX({Fixed Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Fixed Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row
Many thanks,
Adam
Adam Collins
Sr Clinical Development Operations Analyst
Syneos Health
-
It worked! Thank you so much. Really appreciate your time and help.
Help Article Resources
Categories
Check out the Formula Handbook template!