Simplify and add to IF

Hi all.

Currently have the following formula which works fine as is, but I want to add to it and it's getting too confusing for me to work out. Currently I have in cell G6:

=IF(F6="","",IF(O5<>"",6,IF(J5="",G5,IF(G5-(ROUNDDOWN((J5-1)*2,0)/2)<3,G5+6-(ROUNDDOWN((J5-1)*2,0)/2),G5-(ROUNDDOWN((J5-1)*2,0)/2)))))

First 2 parts are fine, if F6 is empty, G6 is empty, if O5 isn't empty then G6 = 6. I need to add another check to the rest of the formula. I need to check if round(H6/result,0) is greater than (if P5 is less than zero) round($E$1*0.05,0) otherwise round((P5+$E$1)*0.05,0). If round(H6/result,0) is greater then I need to add a further 6 to the result.

Just can't think of how to do it. TIA

Originally Posted by davesexcel

=IF(F6="","",IF(O5<>"",6,IF(J5="",G5,IF(G5-(ROUNDDOWN((J5-1)*2,0)/2)<3,G5+6-(ROUNDDOWN((J5-1)*2,0)/2),G5-(ROUNDDOWN((J5-1)*2,0)/2)))))


This looks very confusing, what happens if more then one of the critria is true?

Nothing

With the way they're nested the formula ends after the first true. Each new IF is only used if everything prior to it returns a false.

Well, I've got a working formula, but it definitely isn't pretty. If anyone has any suggestions on how to simplify this I'm more than willing to learn.

=IF(F6="","",IF(O5<>"",6,IF(J5="",IF(ROUND(H6/G5,0)>ROUND(MAX(MAX(P$4:P5)+$E$1,$E$1)*0.05,0),G5+6,G5),IF(G5-(ROUNDDOWN((J5-1)*2,0)/2)<3,G5+6-(ROUNDDOWN((J5-1)*2,0)/2),IF(H6/(G5-(ROUNDDOWN((J5-1)*2,0)/2))>ROUND(MAX(MAX(P$4:P5)+$E$1,$E$1)*0.05,0),G5-(ROUNDDOWN((J5-1)*2,0)/2)+6,G5-(ROUNDDOWN((J5-1)*2,0)/2))))))

not really it looks pretty complicated, but

MAX(MAX(P$4:P5)+$E$1,$E$1)

is (max(P$4:P5,0)+$e$1)

which shortens the formula slightly

Regards

Dav

Originally Posted by Dav

not really it looks pretty complicated, but

MAX(MAX(P$4:P5)+$E$1,$E$1)

is (max(P$4:P5,0)+$e$1)

which shortens the formula slightly

Regards

Dav

It's become even more complicated now, found I missed an are that needed checking so I had to add that as well.

Thanks for that simple change with MAX - sometimes you can miss the easiest things. Even after I looked at it I still needed a minute or so to figure out why that would work.

Well, the completed formula is now some 400+ characters long with the maximum 7 IF statements. If I wanted to make things look better I could split it across several cells, but with the formula being copied down hundreds of rows I'll keep it all locked in one.