Multiple IF/AND formula

[tlynn]

I have been trying to get this single formula to work properly with little luck. Would someone mind explaine to me what I am missing please?

<code>

{=IF($E$8=" "," ",IF($E$8="Auction",IF($C$8<=0.99,($C$8+0.15)),IF(AND($C$8>=1,($C$8<=9.99)),($C$8+0.35),IF(AND($C$8>=10,($C$8<=24.99)),($C$8+0.55),IF(AND($C$8>=25,($C$8<=49.99)),($C$8+1),IF(AND($C$8>=50,($C$8<=199.99)),($C$8+2),IF(AND($C$8>=200,($C$8<=499.99)),($C$8+3),IF($C$8>500,($C$8+4)))))))))}

</code>

basically it checks in E8 to 1) make sure that it is not blank...then 2)it is supposed to check and see if it say "Auction" at which point it should add the appropriate number to cell C8. In the next 2 cells to the right (D8, E8) the formula will be checking for other either "Fixed" or "Store".

Anyway, thanks in advance-

 

[tlynn]

I guess I should also tell everyone what it is doing...when I enter a number into C8 and have "Auction" in E8 I get a False statement...hth.

 

[konew1]

I think you mean If($E$8="","", without the space between the inverted commas, but that probably not your immediate problem. (A blank cell is "". A cell with a space is " ".

You can elimate the AND(C$8 >=1 because you have already tested for <0.99. It doesn't do anything for you, except if the value in C8 is 0.995 it falls into a hole.
Same with all the other AND statements, you should remove the >= component.
Do you have validation to ensure that the string "Auction" in C8 is the same as in your formula. No trailing spaces, etc.

What version of Excel do you run. I can only nest 7 levels of IF statement

 

[alphadown]

Every number under 1 works, but after that, I don't know what's wrong with it..

But there is another method, vlookup.

I made a small table with the cutoff points and the corresponding amount to add in K8:L14 (see below)

0 0.15
1 0.35
10 0.55
25 1
50 2
200 3
500 4

and then used the much smaller formula
=IF(E8="Auction",VLOOKUP(C8,K8:L14,2)+$C$8,"")

you could do the table on another sheet or file if you don't have the space or need it to be neat

 

[tlynn]

Im running 2003 - and the formula as a whole appears to work in that if I change the value in C8 the apprpriate calculations happen. The problem appears when I enter "Auction"/"Fixed"/"Store"into cell E8....I either get a FALSE statement in the Auction column (I8) and the correct formula solution in the Fixed(J8)/Store(K8) columns or any combination there of.

By this I mean:

E8
"Auction"
I8 J8 K8
False Good Good

E8
"Fixed"
I8 J8 K8
Good False Good

E8
"Store"
I8 J8 K8
Good Good False

A basic illustration for sure, and not too confusing I hope...but this is basically what happens.

 

[gwkenny]

I believe you are running into problems because you have gone beyond 7 nested functions and your formula will never respond correctly.

Alphadown gives the best solution because you can have many different pricing levels but it is also very easy to maintain and update.

 

[konew1]

You havent said in the formula what is to happen if it is not Auction in E8
I simplified your formula to
=IF($E$8="","",IF($E$8="Auction",IF(C10<=0.99,(C10+0.15),IF(C10<=9.99,($C10+0.35),IF($C10<=24.99,($C10+0.55),IF($C10<=49.99,($C10+1),IF($C10<=199.99,($C10+2),IF($C10<=499.99,($C10+3),($C10+4))))))))) before I saw your latest posting.

Try

=IF($E$8="","",IF($E$8="Auction",IF(C8< =0.99,(C8+0.15),IF(C8< =9.99,($C8+0.35),IF($C8< =24.99,($C8+0.55),IF($C8< =49.99,($C8+1),IF($C8< =199.99,($C8+2),IF($C8< =499.99,($C8+3),($C8+4))))))),"NOT AN AUCTION"))

 

[tlynn]

Alphadown looks like that works perfectly thank you....and so much easier!