Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tweak formula to not count blanks as 0
I need to modify this array-entered formula to not count blanks as zero. The
challenge seems to be that the cobination of INDIRECT() and N() is converting blanks to zero. {=MIN(N(INDIRECT("Rater" & ROW(1:4) & "!G"&$B$3)))} In plain english, the function looks at the same cell (e.g., G7, when cell B3 is set to 7) across 4 worksheets (Rater1 thru Rater4). I don't need to first function to be MIN, it could be a COUNT. I just need it to stop treating blanks as 0. TIA |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tweak formula to not count blanks as 0
Actually, your INDIRECT function is incorrect, and is generating the #VALUE!
error, which is then converted to 0 by you N function. You do not have your indirect function returning the proper formatting of an array callout. Also, the N function does not return an array of numbers, thus defeating the purpose of your equation. The better question is why you are attempting to use the N function? If you could provide more detail about the data you're working with, and what you are trying to accomplish (MIN number, or count??) we can probably help you. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "andy62" wrote: I need to modify this array-entered formula to not count blanks as zero. The challenge seems to be that the cobination of INDIRECT() and N() is converting blanks to zero. {=MIN(N(INDIRECT("Rater" & ROW(1:4) & "!G"&$B$3)))} In plain english, the function looks at the same cell (e.g., G7, when cell B3 is set to 7) across 4 worksheets (Rater1 thru Rater4). I don't need to first function to be MIN, it could be a COUNT. I just need it to stop treating blanks as 0. TIA |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tweak formula to not count blanks as 0
Hi Luke (and/or others) - Happy to elaborate, although was trying to keep it
simple and not scare off everyone with all the gory details. The function is intended to check if the data in the four cells is a) all 1's, b) all 0's, or c) a mix. If either a) or b) are true the function should produce that single digit. If the function is false then it returns a concatenated string of all the data. Here goes: {=IF(MIN(N(INDIRECT("Rater" & ROW(1:4) & "!B"&$B$3)))=MAX(N(INDIRECT("Rater" & ROW(1:4) & "!B"&$B$3))),MAX(N(INDIRECT("Rater" & ROW(1:4) & "!B"&$B$3))),CONCATENATE(INDIRECT("Rater1!B"&$B$3) ," ",INDIRECT("Rater2!B"&$B$3)," ",INDIRECT("Rater3!B"&$B$3)," ",INDIRECT("Rater4!B"&$B$3)))} I used (with help from someone on this board) the "MIN=MAX" construct to efficiently cover conditions a or b (when all four cells match, MIN=MAX) and return the correct output. It seems to work fine - including the false condition - except when one of the cells is blank. for instance, three 1's and a blank produce the following: 1 1 1 which is all four cells concatenated together. So it is treating the blank cell as 0 or as you suspect an error that gets concerted to 0, thus MIN<MAX. I want it to consider three 1's and a blank as all 1's, and return a 1. Hope that helps! "Luke M" wrote: Actually, your INDIRECT function is incorrect, and is generating the #VALUE! error, which is then converted to 0 by you N function. You do not have your indirect function returning the proper formatting of an array callout. Also, the N function does not return an array of numbers, thus defeating the purpose of your equation. The better question is why you are attempting to use the N function? If you could provide more detail about the data you're working with, and what you are trying to accomplish (MIN number, or count??) we can probably help you. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "andy62" wrote: I need to modify this array-entered formula to not count blanks as zero. The challenge seems to be that the cobination of INDIRECT() and N() is converting blanks to zero. {=MIN(N(INDIRECT("Rater" & ROW(1:4) & "!G"&$B$3)))} In plain english, the function looks at the same cell (e.g., G7, when cell B3 is set to 7) across 4 worksheets (Rater1 thru Rater4). I don't need to first function to be MIN, it could be a COUNT. I just need it to stop treating blanks as 0. TIA |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tweak formula to not count blanks as 0
Hi,
The basic idea is to use something like this =MIN(IF(A4:A11<0,A4:A11,"")) Of course this is an array formula. If you are using N to convert numbers entered as text to numbers then modify this idea to avoid the N: =MIN(IF(A4:A11<0,--A4:A11,"")) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "andy62" wrote: Hi Luke (and/or others) - Happy to elaborate, although was trying to keep it simple and not scare off everyone with all the gory details. The function is intended to check if the data in the four cells is a) all 1's, b) all 0's, or c) a mix. If either a) or b) are true the function should produce that single digit. If the function is false then it returns a concatenated string of all the data. Here goes: {=IF(MIN(N(INDIRECT("Rater" & ROW(1:4) & "!B"&$B$3)))=MAX(N(INDIRECT("Rater" & ROW(1:4) & "!B"&$B$3))),MAX(N(INDIRECT("Rater" & ROW(1:4) & "!B"&$B$3))),CONCATENATE(INDIRECT("Rater1!B"&$B$3) ," ",INDIRECT("Rater2!B"&$B$3)," ",INDIRECT("Rater3!B"&$B$3)," ",INDIRECT("Rater4!B"&$B$3)))} I used (with help from someone on this board) the "MIN=MAX" construct to efficiently cover conditions a or b (when all four cells match, MIN=MAX) and return the correct output. It seems to work fine - including the false condition - except when one of the cells is blank. for instance, three 1's and a blank produce the following: 1 1 1 which is all four cells concatenated together. So it is treating the blank cell as 0 or as you suspect an error that gets concerted to 0, thus MIN<MAX. I want it to consider three 1's and a blank as all 1's, and return a 1. Hope that helps! "Luke M" wrote: Actually, your INDIRECT function is incorrect, and is generating the #VALUE! error, which is then converted to 0 by you N function. You do not have your indirect function returning the proper formatting of an array callout. Also, the N function does not return an array of numbers, thus defeating the purpose of your equation. The better question is why you are attempting to use the N function? If you could provide more detail about the data you're working with, and what you are trying to accomplish (MIN number, or count??) we can probably help you. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "andy62" wrote: I need to modify this array-entered formula to not count blanks as zero. The challenge seems to be that the cobination of INDIRECT() and N() is converting blanks to zero. {=MIN(N(INDIRECT("Rater" & ROW(1:4) & "!G"&$B$3)))} In plain english, the function looks at the same cell (e.g., G7, when cell B3 is set to 7) across 4 worksheets (Rater1 thru Rater4). I don't need to first function to be MIN, it could be a COUNT. I just need it to stop treating blanks as 0. TIA |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tweak formula to not count blanks as 0
But that doesn't accommodate piecing together the reference (the use of
INDIRECT) or working across four worksheets. The underlying data is either 1, 0, . (period), or blank. The 1's and 0's are entered as numbers. To be candid I'm not clear why the previous person used the N function; I thought it was because the INDIRECT function returned numbers as text, but maybe it was for another reason. "Shane Devenshire" wrote: Hi, The basic idea is to use something like this =MIN(IF(A4:A11<0,A4:A11,"")) Of course this is an array formula. If you are using N to convert numbers entered as text to numbers then modify this idea to avoid the N: =MIN(IF(A4:A11<0,--A4:A11,"")) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "andy62" wrote: Hi Luke (and/or others) - Happy to elaborate, although was trying to keep it simple and not scare off everyone with all the gory details. The function is intended to check if the data in the four cells is a) all 1's, b) all 0's, or c) a mix. If either a) or b) are true the function should produce that single digit. If the function is false then it returns a concatenated string of all the data. Here goes: {=IF(MIN(N(INDIRECT("Rater" & ROW(1:4) & "!B"&$B$3)))=MAX(N(INDIRECT("Rater" & ROW(1:4) & "!B"&$B$3))),MAX(N(INDIRECT("Rater" & ROW(1:4) & "!B"&$B$3))),CONCATENATE(INDIRECT("Rater1!B"&$B$3) ," ",INDIRECT("Rater2!B"&$B$3)," ",INDIRECT("Rater3!B"&$B$3)," ",INDIRECT("Rater4!B"&$B$3)))} I used (with help from someone on this board) the "MIN=MAX" construct to efficiently cover conditions a or b (when all four cells match, MIN=MAX) and return the correct output. It seems to work fine - including the false condition - except when one of the cells is blank. for instance, three 1's and a blank produce the following: 1 1 1 which is all four cells concatenated together. So it is treating the blank cell as 0 or as you suspect an error that gets concerted to 0, thus MIN<MAX. I want it to consider three 1's and a blank as all 1's, and return a 1. Hope that helps! "Luke M" wrote: Actually, your INDIRECT function is incorrect, and is generating the #VALUE! error, which is then converted to 0 by you N function. You do not have your indirect function returning the proper formatting of an array callout. Also, the N function does not return an array of numbers, thus defeating the purpose of your equation. The better question is why you are attempting to use the N function? If you could provide more detail about the data you're working with, and what you are trying to accomplish (MIN number, or count??) we can probably help you. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "andy62" wrote: I need to modify this array-entered formula to not count blanks as zero. The challenge seems to be that the cobination of INDIRECT() and N() is converting blanks to zero. {=MIN(N(INDIRECT("Rater" & ROW(1:4) & "!G"&$B$3)))} In plain english, the function looks at the same cell (e.g., G7, when cell B3 is set to 7) across 4 worksheets (Rater1 thru Rater4). I don't need to first function to be MIN, it could be a COUNT. I just need it to stop treating blanks as 0. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Blanks | Excel Discussion (Misc queries) | |||
count blanks in pivot table | Excel Worksheet Functions | |||
Count the blanks | Excel Worksheet Functions | |||
Count IF excluding blanks or zeroes | Excel Worksheet Functions | |||
Count blanks cells | New Users to Excel |