Thread: If Statement with calculations based on data valadation list

I know the title is a mouthful but sadly, so is my dilemma.


I need to create a formula within my sheet that will output a certain calculated figure based on the information within the spreadsheet.

Column E is one way miles
Column F is round trip miles
Column G is "band" or otherwise the data validation drop down list
Column H is the cost based on "if" statements

What I'm trying to do is create separate equations with the cells in Column H that will produce a result based on what is selected in Column G.

For example.

If "Fixed" is selected in Column G, the cell needs to out put an equation that corresponds to the number of miles in Column E (either <30 miles, <50 miles, or >50, miles).
If the miles are less than 30, Column F needs to be multiplied by $25
If the miles are less than 50, Column F needs to be multiplied by $35
If the miles are greater than 50, Column F needs to be multiplied by $40

If "Long" is selected in Column G, the cells needs to do a simple equation =(F$*.50)+4.5

If "Short" is selected in Column G, the cells needs to do a simple equation =(F$*.25)+4.5

Using the list I've provided as an example, Column H should output the following results for Row 2
If "Fixed" = 40
If "Long" = 174.5
If "Short" = 89.5

It tries a forumula to get the "fixed" portion of the cell working (H17) but to no avail. Any help, tips or advice you can offer would be greatly appreciated.EHF Sample.xlsx

Awesome!

That worked for Short and Long but for some reason, fixed is returning really high numbers. Then I noticed it was multiplying by column f and once I tool that out, BINGO!

You are my hero once again NBVC.