Multiple formulas in one cell

[jbsiegmund14]

Current formula I have is as follows:

=IF(I15>="90",1*H15*0.65+N15+M15,IF(AND(I15>=80),1*H15*0.6+N15+M15,IF(AND(I15>=95)*1*0.70*H15+N15+M15,0)))

The first two are working and can not say the same about the third. As soon as I choose a option that has a 95 in I15 drop down list it is defaulting to the "90" formula. I need the formula to read the 95 formula.

If anyone can also tell me how to add this additional formula it would be great. Im guessing it should be IF(AND(I15>=70)*1*0.5**H15+N15+M15

I dont care what order this is put in as long as it works when I choose between 70, 80, 90, and 95 from my drop down list in I15.

Thanks

[MarvinP]

Hi jbsiegmund and welcome to the forum,

You need to use a VLookup Table for your formula. You would put in a value between 0 and 95 and it would return your correct factor. You are looking for the last argument of True.

Read:

https://www.excelcampus.com/function...st-match-true/

or watch
https://exceljet.net/tips/how-to-use...ximate-matches

[guru008]

Hi,

Try This

=IF(AND(I15>=90,I15<95),1*H15*0.65+N15+M15,IF(AND(I15>=80,I15<90),1*H15*0.6+N15+M15,IF(I15>95,1*0.7*H15+N15+M15,0)))

[FDibbins]

Welcome to the forum

A few observations on your formula...
=IF(I15>="90",1*H15*0.65+N15+M15,IF(AND(I15>=80),1*H15*0.6+N15+M15,IF(AND(I15>=95)*1*0.70*H15+N15+M15,0)))

1. whenever you put a value inside "" it becomes and is no longer seen as a value, to remove the "" from around 90
2. you use AND (or OR) if you need to test for a few things, not just 1...AND(name="Bob",age>10)
3. unless the 1* part will change, it is kind of superfluous in there, 1*a number = that number
4. It looks like you have some constants in there, they can often be moved to outside the IF statements
5. If is almost always better to keep the tests in order, either ascending or descending, you have yours mixed up

So, having said all that, perhaps...
=if(I15<=80,0,H15*if(I15>80,0.6,if(I15>90,0.65,0.7))+N15+M15
(untested)

[jbsiegmund14]

This now works when I15 has a value of 95. It is no longer working when I have a value of 90 or 80.

There is ultimately 4 different options in I15. It can either be 95, 90, 80 or 70.

[vlady]

how about below 70 what will happened?

[vlady]

ok so
=LOOKUP(I5,{0,70,80,90,95},{1,0.5,0.6,0.65,0.7})
so if less than 70 will give it a value 1

Please Login or Register  to view this content.