Complicated IF(AND's

[CLEANexcel]

Hello,

So my brain doesn't have the power to figure this one out so if someone has the cerebral capacity to solve this I'd be eternally grateful.

Attached is a 'calculator' that works out the volume of chefs wear needed for each of the hotels within the hotel group I work for. The plan is to send this to each of the managers at each of the hotels so they can simply fill it in, save it and return in to me so I can collate the information.

The problem is, I'm struggling with some very complicated IF AND's and there are calculations happening that I don't want to happen.

I'm going to assume that whoever is reading this is so adept at excel I won' need to explain too much because they'll figure out the issue just by using it. However, I'll be here to answer questions.

I will point out though that cells G20, 22 & 24 is where calculations happen before I want them to. Those cells refer a usually hidden cell (H15) which refer to other cells.

I leave it in your capable hands.

I look forward to answering any questions.

 

[CLEANexcel]

I can't seem to attach, help with that first would be appreciated

 

[Stiuart_W]

I can't seem to attach, help with that first would be appreciated

You can't attach a file to this forum. You need to use something like Dropbox

Don't use anything that means people need to log into or link their Facebook (eg) account to.

 

[Peter_SSs]

You need to use something like Dropbox

Better still is to post small dummy sample data directly in your post so that people who don't want to download unknown files, or are prevented from doing that by workplace security, can still help. A link in my signature block below has suggestions.

 

[CLEANexcel]

Will Google drive do?

https://drive.google.com/open?id=0B_eag_jlhEH8dkNBY3dxSzZjVDA

 

[CLEANexcel]

So the cells I'm concerned with are labelled 'Trousers', 'Bib Aprons' & 'Waist Aprons'. They all refer to cell H14 which has the following formula within.

=IF(AND(C11<>"Not Selected",C12<>"Not Selected",C13<>"Not Selected",C14<>"Not Selected"),"All Cells Selected",IF(AND(C11="Not Selected",C12="Not Selected",C13="Not Selected",C14="Not Selected"),"No Cells Selected",IF(AND(C11<>"Not Selected",C12="Not Selected",C13="Not Selected",C14="Not Selected"),"Head Chef Selected",IF(AND(C11="Not 
Selected",C12<>"Not Selected",C13="Not Selected",C14="Not Selected"),"Sous Chef Selected",IF(AND(C11="Not Selected",C12="Not Selected",C13<>"Not Selected",C14="Not Selected"),"Chefs Selected",IF(AND(C11="Not Selected",C12="Not Selected",C13="Not Selected",C14<>"Not Selected"),"KP Selected",IF(AND(C11<>"Not Selected",C12<>"Not Selected",C13="Not Selected",C14="Not Selected"),"HC&SC Selected",IF(AND(C11<>"Not Selected",C12="Not Selected",C13<>"Not Selected",C14="Not Selected"),"HC&C Selected",IF(AND(C11<>"Not Selected",C12="Not Selected",C13="Not Selected",C14<>"Not Selected"),"HC&KP Selected",IF(AND(C11="Not Selected",C12<>"Not Selected",C13<>"Not Selected",C14="Not Selected"),"SC&C Selected",IF(AND(C11="Not Selected",C12<>"Not Selected",C13="Not Selected",C14<>"Not Selected"),"SC&KP Selected",IF(AND(C11="Not Selected",C12="Not Selected",C13<>"Not Selected",C14<>"Not Selected"),"C&KP Selected",IF(AND(C11<>"Not Selected",C12<>"Not Selected",C13<>"Not Selected",C14="Not Selected"),"KP Not Selected",IF(AND(C11<>"Not Selected",C12<>"Not Selected",C13="Not Selected",C14<>"Not Selected"),"C Not Slected",IF(AND(C11<>"Not Selected",C12="Not Selected",C13<>"Not Selected",C14<>"Not Selected"),"SC Not Selected",IF(AND(C11="Not Selected",C12<>"Not Selected",C13<>"Not Selected",C14<>"Not Selected"),"HC Not Selected"))))))))))))))))

This gives the following outcomes when certain cells between C11:G14 are selected from drop down menus.

All cells selected
No cells selected
Head Chef Selected
Sous Chef Selected
Chefs Selected
KP Selected
HC&SC Selected
HC&C Selected
HC&KP Selected
SC&C Selected
SC&KP Selected
C&KP Selected
KP Not Selected
C Not Selected
SC Not Selected
HC Not Selected

The cells labelled 'Trousers', 'Bib Aprons' & 'Waist Aprons' will then make calculations based on the value in H14.

The 'Trousers' cell has the following formula

=IFERROR(IF(AND(H15="All Cells Selected"),SUM(C11:C14)*SUM(E11:E14)*2+SUM(C11:C14),IF(AND(H15="No Cells Selected"),"0",IF(AND(H15="Head Chef Selected"),(C11*E11)*2+C11,IF(AND(H15="Sous Chef Selected"),(C12*E12)*2+C12,IF(AND(H15="Chefs Selected"),(C13*E13)*2+C13,IF(AND(H15="KP Selected"),(C14*E14)*2+C14,IF(AND(H15="HC&SC Selected"),SUM(C11:C12)*SUM(E11:E12)*2+SUM(C11:C12),IF(AND(H15="HC&C Selected"),SUM(C11,C13)*SUM(E11,E13)*2+SUM(E11,E13),IF(AND(H15="HC&KP Selected"),SUM(C11,C14)*SUM(E11,E14)*2+SUM(C11,C13),IF(AND(H15="SC&C Selected"),SUM(C12,C13)*SUM(E12,E13)*2+SUM(C12,C13),IF(AND(H15="SC&KP Selected"),SUM(C12,C14)*SUM(E12,E14)*2+(C12,C14),IF(AND(H15="C&KP Selected"),SUM(C13:C14)*SUM(E13:E14)*2+SUM(C13:C14),IF(AND(H15="HC Not Selected"),SUM(C12:C14)*SUM(E12:E14)*2+SUM(C12:C14),IF(AND(H15="SC Not Selected"),SUM(C11,C13:C14)*SUM(E11,E13:E14)*2+SUM(C11,C13:C14),IF(AND(H15="C Not Selected"),SUM(C11:C12,C14)*SUM(E11:E12,E14)*2+SUM(C11:C12,C14),IF(AND(H15="KP Not Selected"),SUM(C11:C13)*SUM(E11:E13)*2+SUM(C11:C13),"0")))))))))))))))),"0")

The 'Bib Aprons' Cell has the following formula

=IFERROR(IF(AND(H15="All Cells Selected"),SUM(C11:C14)*SUM(G11:G14)*2+SUM(C11:C14),IF(AND(H15="No Cells Selected"),"0",IF(AND(H15="Head Chef Selected"),(C11*G11)*2+C11,IF(AND(H15="Sous Chef Selected"),(C12*G12)*2+C12,IF(AND(H15="Chefs Selected"),(C13*F13)*2+C13,IF(AND(H15="KP Selected"),(C14*G14)*2+C14,IF(AND(H15="HC&SC Selected"),SUM(C11:C12)*SUM(G11:G12)*2+SUM(C11:C12),IF(AND(H15="HC&C Selected"),SUM(C11,C13)*SUM(G11,G13)*2+SUM(E11,E13),IF(AND(H15="HC&KP Selected"),SUM(C11,C14)*SUM(G11,G14)*2+SUM(C11,C13),IF(AND(H15="SC&C Selected"),SUM(C12,C13)*SUM(G12,G13)*2+SUM(C12,C13),IF(AND(H15="SC&KP Selected"),SUM(C12,C14)*SUM(G12,G14)*2+SUM(C12,C14),IF(AND(H15="C&KP Selected"),SUM(C13:C14)*SUM(G13:G14)*2+SUM(C13:C14),IF(AND(H15="HC Not Selected"),SUM(C12:C14)*SUM(G12:G14)*2+SUM(C12:C14),IF(AND(H15="SC Not Selected"),SUM(C11,C13:C14)*SUM(G11,G13:G14)*2+SUM(C11,C13:C14),IF(AND(H15="C Not Selected"),SUM(C11:C12,C14)*SUM(G11:G12,G14)*2+SUM(C11:C12,C14),IF(AND(H15="KP Not Selected"),SUM(C11:C13)*SUM(G11:G13)*2+SUM(C11:C13),"0")))))))))))))))),"0")

And the 'Waist Aprons' Cell has the following formula

=IFERROR(IF(AND(H15="All Cells Selected"),SUM(C11:C14)*SUM(F11:F14)*2+SUM(C11:C14),IF(AND(H15="No Cells Selected"),"0",IF(AND(H15="Head Chef Selected"),(C11*F11)*2+C11,IF(AND(H15="Sous Chef Selected"),(C12*F12)*2+C12,IF(AND(H15="Chefs Selected"),(C13*F13)*2+C13,IF(AND(H15="KP Selected"),(C14*F14)*2+C14,IF(AND(H15="HC&SC Selected"),SUM(C11:C12)*SUM(F11:F12)*2+SUM(C11:C12),IF(AND(H15="HC&C Selected"),SUM(C11,C13)*SUM(F11,F13)*2+SUM(E11,E13),IF(AND(H15="HC&KP Selected"),SUM(C11,C14)*SUM(F11,F14)*2+SUM(C11,C13),IF(AND(H15="SC&C Selected"),SUM(C12,C13)*SUM(F12,F13)*2+SUM(C12,C13),IF(AND(H15="SC&KP Selected"),SUM(C12,C14)*SUM(F12,F14)*2+SUM(C12,C14),IF(AND(H15="C&KP Selected"),SUM(C13:C14)*SUM(F13:F14)*2+SUM(C13:C14),IF(AND(H15="HC Not Selected"),SUM(C12:C14)*SUM(F12:F14)*2+SUM(C12:C14),IF(AND(H15="SC Not Selected"),SUM(C11,C13:C14)*SUM(F11,F13:F14)*2+SUM(C11,C13:C14),IF(AND(H15="C Not Selected"),SUM(C11:C12,C14)*SUM(F11:F12,F14)*2+SUM(C11:C12,C14),IF(AND(H15="KP Not Selected"),SUM(C11:C13)*SUM(F11:F13)*2+SUM(C11:C13),"0")))))))))))))))),"0")

The problem that's happening is that these cells start calculating too early, before any real relevant information is selected by the drop down selection. Illustrated in by the following image.