multifunction formula

[Mstout9026]

Hi, I'm hoping someone can help me out on this one.

I'm in need of a multifunctional formula (in one cell) with the following criteria: Be able to enter only one product "A-E"; have 4 separate equations for each letter "A-E" (example below), then have the entered letter's equation take over to price a particular volume.
Currently, I have the following equations elsewhere in the worksheet which individually breaks down smaller volumes of "A", "B", etc.

=IF(E153>0.01,E153<=9)*(E153*3200)
=IF(E153>9,E153<=19)*(E153*3100)
=IF(E153>19,E153<=37)*(E153*3000)
=IF(E153>37,E153<250)*(E153*2900)

E153 represents the volume in the above formulas and the price point is represented by 3200,3100, etc. In the above 4 formulas, only one will obviously match it's volume criteria and the rest are left blank. This repeats itself a couple of different times throughout the worksheet for additional volumes in other areas. There's a formula in place for the SUM of those volumes, which will represent the new overall volume. Due to volume change, the new SUM would have to be rerun through the above formulas (for whichever product entered) for possible price point change. Here's a partial sample of how I was understanding it. I only put two equations with "A" and "B" in this formula to see if it would work. Guess what? It didn't. Hopefully, I put enough info here for this to make sense. I'm a novice at this, so any help would be appreciated.

=If(E150="A",CHOOSE((E153>.1,E153<=9)*(E153*3200)),if((E153>9,E153<=19)*(E153*3100)),IF(E150="B",CHOOSE((F153>.1,F153<=9)*(F153*2850)),IF….C, D, E, etc. .....you can stop laughing now .....

Thanks!

Mike

Skyline Job Costing Form 2014.xlsx The problem area is E159:E166, I put some general notes to the right. Anything below 169 hasn't been addressed yet. Thanks much in advance for any help.

[tuph]

SUMPRODUCT might help but it would be much easier to help you if you can attach your workbook. Remember to remove/replace any sensitive data before uploading. (Attaching your workbook means no-one needs to set up the spreadsheet to test possible solutions.)

[FDibbins]

Perhaps if you provided some sample answers and how you got them, it would help?

[cffndncr]

I (kinda) think I follow.

Since your categories are all based on one cell and are mutually exclusive, you can group them into one formula.

=IF(E153<=0.01,"",IF(E153<=9,E153*3200,IF(E153<=19,E153*3100,IF(E153<=37,E153*3000,IF(E153<250,E153*2900,"XXXX")))))

Here it will return a null reference if E153 is <=0.01. Where I have shown XXXX at the end, you should enter the value you want if E153>=250

[cffndncr]

Actually now that I think about it, you'd probably be better off using a table showing your volume-price points. This way if you change the price points in the future, you can just update the table instead of (tediously) going through every formula and updating them

[FDibbins]

@ cffn, I was thinking along those lines too, but was hoping to see what the expected outcomes might be 1st

[cffndncr]

Lol you beat me to the punch the other day with something like this, thought I'd try to get one back :P