annabee

New Member
Joined
Apr 17, 2019
Messages
3
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!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,224
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 17, 2019
Messages
3
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,224
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,224
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

annabee

New Member
Joined
Apr 17, 2019
Messages
3
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,224
Office Version
  1. 365
Platform
  1. Windows
Using the last example your formula would be
Code:
=(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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top