Dividing Nested IF/AND sum

[annabee]

Hi!

I'm still learning to use nested formulas, and I'm trying to divide this particular formulate below by 7, but when I put /7 the output is incorrect (in this case the sum is 24 but the output of when I put /7 I get 20 - which is definitely not right). Please help!

=IF(AND(C86=0,C86<6),1,IF(AND(C86>5,C86<16),2,IF(AND(C86>15,C86<25),3,IF(AND(C86>24,C86<35),4,IF(C86>34,5,0)))))+IF(AND(D86=0,D86<6),1,IF(AND(D86>5,D86<16),2,IF(AND(D86>15,D86<25),3,IF(AND(D86>24,D86<35),4,IF(D86>34,5,0)))))+IF(AND(E86=0,E86<6),1,IF(AND(E86>5,E86<16),2,IF(AND(E86>15,E86<25),3,IF(AND(E86>24,E86<35),4,IF(E86>34,5,0)))))+IF(AND(F86=0,F86<6),1,IF(AND(F86>5,F86<16),2,IF(AND(F86>15,F86<25),3,IF(AND(F86>24,F86<35),4,IF(F86>34,5,0)))))+IF(AND(G86=0,G86<6),1,IF(AND(G86>5,G86<16),2,IF(AND(G86>15,G86<25),3,IF(AND(G86>24,G86<35),4,IF(G86>34,5,0)))))+IF(AND(H86=0,H86<6),1,IF(AND(H86>5,H86<16),2,IF(AND(H86>15,H86<25),3,IF(AND(H86>24,H86<35),4,IF(H86>34,5,0)))))+IF(AND(I86=0,I86<6),1,IF(AND(I86>5,I86<16),2,IF(AND(I86>15,I86<25),3,IF(AND(I86>24,I86<35),4,IF(I86>34,5,0)))))


Thank you in advance!

 

[Fluff]

Hi & welcome to MrExcel.
You need to wrap the entire formula in brackets before dividing by7, otherwise you are only dividing the last part of it.
Also this part
AND(C86=0,C86<6)
does not make sense what is it meant to be doing?

 

[annabee]

Hi & welcome to MrExcel.
You need to wrap the entire formula in brackets before dividing by7, otherwise you are only dividing the last part of it.
Also this part
AND(C86=0,C86<6)
does not make sense what is it meant to be doing?

Ah thank you! You're right - I just rewrote that part, the formula now looks like this:

=IF(C7<5,1,IF(AND(C7>4,C7<15),2,IF(AND(C7>14,C7<25),3,IF(AND(C7>24,C7<35),4,IF(C7>34,5,0)))))+IF(D7<5,1,IF(AND(D7>4,D7<15),2,IF(AND(D7>14,D7<25),3,IF(AND(D7>24,D7<35),4,IF(D7>34,5,0)))))+IF(E7<5,1,IF(AND(E7>4,E7<15),2,IF(AND(E7>14,E7<25),3,IF(AND(E7>24,E7<35),4,IF(E7>34,5,0)))))+IF(F7<5,1,IF(AND(F7>4,F7<15),2,IF(AND(F7>14,F7<25),3,IF(AND(F7>24,F7<35),4,IF(F7>F34,5,0)))))+IF(G7<5,1,IF(AND(G7>4,G7<15),2,IF(AND(G7>14,G7<25),3,IF(AND(G7>24,G7<35),4,IF(G7>34,5,0)))))+IF(H7<5,1,IF(AND(H7>4,H7<15),2,IF(AND(H7>14,H7<25),3,IF(AND(H7>24,H7<35),4,IF(H7>34,5,0)))))+IF(I7<5,1,IF(AND(I7>4,I7<15),2,IF(AND(I7>14,I7<25),3,IF(AND(I7>24,I7<35),4,IF(I7>34,5,0)))))

Regarding the brackets would it looks like this? Because that's not working for me. I appreciate you help!

{=IF(C8<5,1,IF(AND(C8>4,C8<15),2,IF(AND(C8>14,C8<25),3,IF(AND(C8>24,C8<35),4,IF(C8>34,5,0)))))+IF(D8<5,1,IF(AND(D8>4,D8<15),2,IF(AND(D8>14,D8<25),3,IF(AND(D8>24,D8<35),4,IF(D8>34,5,0)))))+IF(E8<5,1,IF(AND(E8>4,E8<15),2,IF(AND(E8>14,E8<25),3,IF(AND(E8>24,E8<35),4,IF(E8>34,5,0)))))+IF(F8<5,1,IF(AND(F8>4,F8<15),2,IF(AND(F8>14,F8<25),3,IF(AND(F8>24,F8<35),4,IF(F8>F35,5,0)))))+IF(G8<5,1,IF(AND(G8>4,G8<15),2,IF(AND(G8>14,G8<25),3,IF(AND(G8>24,G8<35),4,IF(G8>34,5,0)))))+IF(H8<5,1,IF(AND(H8>4,H8<15),2,IF(AND(H8>14,H8<25),3,IF(AND(H8>24,H8<35),4,IF(H8>34,5,0)))))+IF(I8<5,1,IF(AND(I8>4,I8<15),2,IF(AND(I8>14,I8<25),3,IF(AND(I8>24,I8<35),4,IF(I8>34,5,0)))))}/7

 

[Fluff]

If I've understood correctly you can re-write each section of that formula like
=IF(C7<5,1,IF(C7<15,2,IF(C7<25,3,IF(C7<35,4,5))))
As for the brackets, you need to add a normal bracket before the if =(IF and then add a closing one to the end, then divide by 7 5,0))))))/7

 

[Fluff]

Each section could also be written like
=LOOKUP(C8,{0,5,15,25,35},{1,2,3,4,5})

 

[annabee]

Each section could also be written like
=LOOKUP(C8,{0,5,15,25,35},{1,2,3,4,5})

That's amazing, I didn't know about LOOKUP. Super useful - thank you.

Regarding the brackets, I'm still struggling as from what I'm finding they can't be entered manually, so I put them in via CTRL+SHIFT+ENTER, but then when I go to type in the /7, the brackets disappear. Assuming they're still there when I'm in edit mode, I typed in /7 but it still didn't result in the correct output. Any ideas?

 

[Fluff]

Using the last example your formula would be

=(LOOKUP(C8,{0,5,15,25,35},{1,2,3,4,5})+LOOKUP(D8,{0,5,15,25,35},{1,2,3,4,5})+LOOKUP(E8,{0,5,15,25,35},{1,2,3,4,5})+LOOKUP(F8,{0,5,15,25,35},{1,2,3,4,5})+LOOKUP(G8,{0,5,15,25,35},{1,2,3,4,5})+LOOKUP(H8,{0,5,15,25,35},{1,2,3,4,5})+LOOKUP(I8,{0,5,15,25,35},{1,2,3,4,5}))/7