Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make formula more simple --- array?
Hi group,
I just have a quick question. I understand arrays (mostly?) and have found their use extremely convenient especially when typing out a "megaformula". However, I have this situation that has been bugging me lately in my various Excel work. Take the following formula: =IF (OR (W4 = "FAIL", W8 = "FAIL", W13 = "FAIL"), "FAIL", "PASS") That works perfectly, but it becomes a hassle when there's more than 3 values I'm comparing. Of course this can also be written: =IF (AND (W4 = "PASS", W8 = "PASS", W13 = "PASS"), "PASS", "FAIL") Same thing, works perfectly, but also becomes a hassle when there's more than 3 cells I'm comparing. What I'm looking for is a way to shorten this formula, like an array does with countless AND() functions. (W4:W8 -- except in this case the cells are not next to eachother) Make sense? Is that possible? Thank you in advance, Nikki |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make formula more simple --- array?
Hi Nikki,
If your cells are adjacent: =IF(SUMPRODUCT(--(A1:A20="PASS"))=COUNTA(A1:A20),"PASS","FAIL") =IF(SUMPRODUCT(--(A1:A20="FAIL"))0,"FAIL","PASS") If not, array-enter for example: =IF(SUM(--(A1="FAIL"),--(A7="FAIL"),--(A9="FAIL"))0,"FAIL","PASS") Regards, Bernd |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make formula more simple --- array?
=IF(SUMPRODUCT(--(T(OFFSET(W4:W100,{0,4,5,6},0,1,1))="PASS"))0,"PA SS","FAIL")
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nikki" wrote in message oups.com... Hi group, I just have a quick question. I understand arrays (mostly?) and have found their use extremely convenient especially when typing out a "megaformula". However, I have this situation that has been bugging me lately in my various Excel work. Take the following formula: =IF (OR (W4 = "FAIL", W8 = "FAIL", W13 = "FAIL"), "FAIL", "PASS") That works perfectly, but it becomes a hassle when there's more than 3 values I'm comparing. Of course this can also be written: =IF (AND (W4 = "PASS", W8 = "PASS", W13 = "PASS"), "PASS", "FAIL") Same thing, works perfectly, but also becomes a hassle when there's more than 3 cells I'm comparing. What I'm looking for is a way to shorten this formula, like an array does with countless AND() functions. (W4:W8 -- except in this case the cells are not next to eachother) Make sense? Is that possible? Thank you in advance, Nikki |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make formula more simple --- array?
Maybe something like this?:
=SUMPRODUCT(--(ROW(W4:W27)={4,8,13,16,22,27})*(W4:W27="PASS")) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Nikki" wrote: Hi group, I just have a quick question. I understand arrays (mostly?) and have found their use extremely convenient especially when typing out a "megaformula". However, I have this situation that has been bugging me lately in my various Excel work. Take the following formula: =IF (OR (W4 = "FAIL", W8 = "FAIL", W13 = "FAIL"), "FAIL", "PASS") That works perfectly, but it becomes a hassle when there's more than 3 values I'm comparing. Of course this can also be written: =IF (AND (W4 = "PASS", W8 = "PASS", W13 = "PASS"), "PASS", "FAIL") Same thing, works perfectly, but also becomes a hassle when there's more than 3 cells I'm comparing. What I'm looking for is a way to shorten this formula, like an array does with countless AND() functions. (W4:W8 -- except in this case the cells are not next to eachother) Make sense? Is that possible? Thank you in advance, Nikki |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make formula more simple --- array?
Ooops! I only pasted part of the formula. Here's the whole thing:
=IF(SUMPRODUCT(--(ROW(W4:W27)={4,8,13,16,22,27})*(W4:W27="PASS"))=6 ,"PASS","FAIL") Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Maybe something like this?: =SUMPRODUCT(--(ROW(W4:W27)={4,8,13,16,22,27})*(W4:W27="PASS")) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Nikki" wrote: Hi group, I just have a quick question. I understand arrays (mostly?) and have found their use extremely convenient especially when typing out a "megaformula". However, I have this situation that has been bugging me lately in my various Excel work. Take the following formula: =IF (OR (W4 = "FAIL", W8 = "FAIL", W13 = "FAIL"), "FAIL", "PASS") That works perfectly, but it becomes a hassle when there's more than 3 values I'm comparing. Of course this can also be written: =IF (AND (W4 = "PASS", W8 = "PASS", W13 = "PASS"), "PASS", "FAIL") Same thing, works perfectly, but also becomes a hassle when there's more than 3 cells I'm comparing. What I'm looking for is a way to shorten this formula, like an array does with countless AND() functions. (W4:W8 -- except in this case the cells are not next to eachother) Make sense? Is that possible? Thank you in advance, Nikki |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make formula more simple --- array?
On Apr 11, 10:40 am, Ron Coderre
wrote: Ooops! I only pasted part of the formula. Here's the whole thing: =IF(SUMPRODUCT(--(ROW(W4:W27)={4,8,13,16,22,27})*(W4:W27="PASS"))=6 ,"PASS",*"FAIL") Does that help? <snip Yes, Ron, Thank you; I knew it could be done more efficiently somehow. Thanks to everyone else too, but this one fits my needs for this particular problem (everything being in one row/column). :) Nikki |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make formula more simple --- array?
Hi Nikki,
A little warning if you really intend to use some of the suggested formulas: Test them by inserting some rows between the cells you compare. All suggestions are worth to be analyzed and one can learn quite a lot, I think. But some of them are vulnerable against insertions/deletions etc... Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I make a simple check mark column? | Excel Discussion (Misc queries) | |||
Can I make a simple fill-in calculator for webpage using Excel? | Excel Discussion (Misc queries) | |||
Simple question- how to make X axis on bottom of chart | Charts and Charting in Excel | |||
Help please simple drop and drag I can't make work | Excel Worksheet Functions | |||
Make it more simple or intuitive to do simple things | Charts and Charting in Excel |