Combining If + and commands

[Peterw_2506]

Hi All,

Does anybody know how to solve a little problem. Below is a two part formula; the first part give a date in cell C12 if open exists in any of the cell targeted, the second part puts the CLOSED in cell C12 if a 0 appears in the targeted cell. Both formula's work individually but when combined as shown in the formula below I get the result "#value!".

=IF(G12="open",E12+12,IF(K12="open",I12+12,IF(O12="open",M12+12,
IF(S12="open",Q12+12,IF(W12="open",U12+12,IF(N12="open",L12+12,
IF(AA12="open",Y12+12,""))))))),IF(H12=0,"CLOSED",IF(L12=0,"CLOSED"))

Does anybody know a work around for this problem please.

Thank you in advance, Peter

 

[Michael M]

Peter
What happens if H12 OR L12 is NOT = 0,
you haven't allowed for this in your formula
Also, unless your using Excel 2007 or higher, you have too many IF's

 

[Blade Hunter]

Maybe this:

=IF(LEN(CONCATENATE(G12,K12,O12,S12,W12,N12,AA12))<>LEN(SUBSTITUTE(CONCATENATE(G12,K12,O12,S12,W12,N12,AA12),"open","")),INDIRECT(ADDRESS(12,INDEX(MATCH("open",12:12),1)-2))+12,IF(OR(H12=0,L12=0),"CLOSED"))

Checks for open in the cells you specify, if found it looks up the position, offsets 2 to the left and adds 12. If it is not found then it looks up H12 or L12 for 0, if found it puts in CLOSED

 

[Aladin Akyurek]

Hi All,

Does anybody know how to solve a little problem. Below is a two part formula; the first part give a date in cell C12 if open exists in any of the cell targeted, the second part puts the CLOSED in cell C12 if a 0 appears in the targeted cell. Both formula's work individually but when combined as shown in the formula below I get the result "#value!".

=IF(G12="open",E12+12,IF(K12="open",I12+12,IF(O12="open",M12+12,
IF(S12="open",Q12+12,IF(W12="open",U12+12,IF(N12="open",L12+12,
IF(AA12="open",Y12+12,""))))))),IF(H12=0,"CLOSED",IF(L12=0,"CLOSED"))

Does anybody know a work around for this problem please.

Thank you in advance, Peter

What part (longer IF vs shorter IF) has the priority if they are not mutually exclusive?

 

[Peterw_2506]

Wow, Blade Hunter, you are good I haven't seen this sort of code before (its good) I'm showing my infancy in excel here and I am more than certainly going to investigate some of the new commands you have shown me. OK down to business; on the row that has has a zero in it, the formula works and "close" appears in cell C12 but when I copy it into another row that "open" appears in (and no zero exists yet) I still get "close" coming up.

By the way, I shortened the number of zero cells as it was a principle of the concept but with the formula you have provided me I'm not certain how to elongate the formula. The cells that have zero in it are; H12, L12, P12, T12, X12, AB12. any other numeral that appears in the cells mentioned should be disregarded. Just for clarity if "open" doesn't exist, and other numerals do exists in what I stated above, cell C12 should be present itself as a blank cell. Only when "open" occurs should a date appears.

If you can help me I am very grateful. Once again thank you.

PS - What part of Sydney you from, I use to live in Killara till I moved to the Middle East to work.

regards, Peter

 

[Aladin Akyurek]

Do you have an answer to the question posed in post #4?

 

[Peterw_2506]

Hi Aladin,

Sorry I've been in and out of meetings all morning - don't we love it.

To answer your question, "open" takes priority when zero is reached the issue is deemed closed and hence "closed" is put in cell C12. As explained in an early reply the cells that will have zero in at sometime in the future, are (H12, L12, P12, T12, X12, AB12).

If you can help me I would be very grateful. Thank you in advance. Peter

 

[Aladin Akyurek]

Hi Aladin,

Sorry I've been in and out of meetings all morning - don't we love it.

To answer your question, "open" takes priority when zero is reached the issue is deemed closed and hence "closed" is put in cell C12. As explained in an early reply the cells that will have zero in at sometime in the future, are (H12, L12, P12, T12, X12, AB12).

If you can help me I would be very grateful. Thank you in advance. Peter

Suppose G12 = Open and H12 = 0? is this possible at the same time? If possible, what must happen in C12?

 

[Peterw_2506]

Aladin,

If H12 has a numeral, a date will appear by this point in G12. H12 represents the number of outstanding issues that need to be addressed. Unless H12 has a zero, C12 will remain blank at this point

regards, Peter

 

[Aladin Akyurek]

Aladin,

If H12 has a numeral, a date will appear by this point in G12. H12 represents the number of outstanding issues that need to be addressed. Unless H12 has a zero, C12 will remain blank at this point

regards, Peter

Your original post tests whether G12 is open while you now claim that a date will appear in G12. It's essential that you describe your problem unambiguously.