Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting-Don't use Blanks inside formula
Hi. I am currently using the STDEV formula. Is there a way, that i can make
the formula use data from nonblank cells, only. For example: A1, A2, A5 will have numbers listed. A3 & A4 are blank. Can i get the formula to use only A1, A2, & A5? Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting-Don't use Blanks inside formula
Hi!
Empty cells and cells that contain text are ignored. You don't need to specifically exclude empty cells. Biff "Angela" wrote in message ... Hi. I am currently using the STDEV formula. Is there a way, that i can make the formula use data from nonblank cells, only. For example: A1, A2, A5 will have numbers listed. A3 & A4 are blank. Can i get the formula to use only A1, A2, & A5? Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting-Don't use Blanks inside formula
I hit send before I was done!
This array entered formula specifically excludes empty cells: =STDEV(IF(A1:A5<"",A1:A5)) But it returns the exact same result as: =STDEV(A1:A5) Biff "Biff" wrote in message ... Hi! Empty cells and cells that contain text are ignored. You don't need to specifically exclude empty cells. Biff "Angela" wrote in message ... Hi. I am currently using the STDEV formula. Is there a way, that i can make the formula use data from nonblank cells, only. For example: A1, A2, A5 will have numbers listed. A3 & A4 are blank. Can i get the formula to use only A1, A2, & A5? Any help would be greatly appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting-Don't use Blanks inside formula
Biff, What if i need the same result as below, but was using the formula
=AVEDEV, =AVERAGE & =MEDIAN. Can i make it do the same thing? I tried using the IF statement you wrote below on them, and it didn't work. It does work on the =STDEV, though. Thank you in advance for all your help. You really know your formulas. Thanks :-) "Biff" wrote: I hit send before I was done! This array entered formula specifically excludes empty cells: =STDEV(IF(A1:A5<"",A1:A5)) But it returns the exact same result as: =STDEV(A1:A5) Biff "Biff" wrote in message ... Hi! Empty cells and cells that contain text are ignored. You don't need to specifically exclude empty cells. Biff "Angela" wrote in message ... Hi. I am currently using the STDEV formula. Is there a way, that i can make the formula use data from nonblank cells, only. For example: A1, A2, A5 will have numbers listed. A3 & A4 are blank. Can i get the formula to use only A1, A2, & A5? Any help would be greatly appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting-Don't use Blanks inside formula
Angela,
All those functions ignore blanks automatically. With the =STDEV(IF(A1:A5<"",A1:A5)) method, you need to use Ctrl-Shift-Enter rather than just Enter. That's what Biff meant by Array formula.... HTH, Bernie MS Excel MVP "Angela" wrote in message ... Biff, What if i need the same result as below, but was using the formula =AVEDEV, =AVERAGE & =MEDIAN. Can i make it do the same thing? I tried using the IF statement you wrote below on them, and it didn't work. It does work on the =STDEV, though. Thank you in advance for all your help. You really know your formulas. Thanks :-) "Biff" wrote: I hit send before I was done! This array entered formula specifically excludes empty cells: =STDEV(IF(A1:A5<"",A1:A5)) But it returns the exact same result as: =STDEV(A1:A5) Biff "Biff" wrote in message ... Hi! Empty cells and cells that contain text are ignored. You don't need to specifically exclude empty cells. Biff "Angela" wrote in message ... Hi. I am currently using the STDEV formula. Is there a way, that i can make the formula use data from nonblank cells, only. For example: A1, A2, A5 will have numbers listed. A3 & A4 are blank. Can i get the formula to use only A1, A2, & A5? Any help would be greatly appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting-Don't use Blanks inside formula
Bernie, Please don't hate me. But I must be doing something wrong because it
isn't coming up with the same thing. Example: A1=1, B1=0, C1=2, D1=0, E1=3, D1=0, E1=4, F1:Z1=0 If using the =AVEDEV; =AVERAGE; =MEDIAN, it comes up with a different answer than if i were to use the =AVEDEV(IF(A1:Z1<"",A1:Z1)). It sounds weird, but it actually works until i enter data on the 4th cell. Am i doing something wrong? "Bernie Deitrick" wrote: Angela, All those functions ignore blanks automatically. With the =STDEV(IF(A1:A5<"",A1:A5)) method, you need to use Ctrl-Shift-Enter rather than just Enter. That's what Biff meant by Array formula.... HTH, Bernie MS Excel MVP "Angela" wrote in message ... Biff, What if i need the same result as below, but was using the formula =AVEDEV, =AVERAGE & =MEDIAN. Can i make it do the same thing? I tried using the IF statement you wrote below on them, and it didn't work. It does work on the =STDEV, though. Thank you in advance for all your help. You really know your formulas. Thanks :-) "Biff" wrote: I hit send before I was done! This array entered formula specifically excludes empty cells: =STDEV(IF(A1:A5<"",A1:A5)) But it returns the exact same result as: =STDEV(A1:A5) Biff "Biff" wrote in message ... Hi! Empty cells and cells that contain text are ignored. You don't need to specifically exclude empty cells. Biff "Angela" wrote in message ... Hi. I am currently using the STDEV formula. Is there a way, that i can make the formula use data from nonblank cells, only. For example: A1, A2, A5 will have numbers listed. A3 & A4 are blank. Can i get the formula to use only A1, A2, & A5? Any help would be greatly appreciated. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting-Don't use Blanks inside formula
Angela,
Blanks are not zeroes, and Excel treats them differently. Which do you have? HTH, Bernie MS Excel MVP "Angela" wrote in message ... Bernie, Please don't hate me. But I must be doing something wrong because it isn't coming up with the same thing. Example: A1=1, B1=0, C1=2, D1=0, E1=3, D1=0, E1=4, F1:Z1=0 If using the =AVEDEV; =AVERAGE; =MEDIAN, it comes up with a different answer than if i were to use the =AVEDEV(IF(A1:Z1<"",A1:Z1)). It sounds weird, but it actually works until i enter data on the 4th cell. Am i doing something wrong? "Bernie Deitrick" wrote: Angela, All those functions ignore blanks automatically. With the =STDEV(IF(A1:A5<"",A1:A5)) method, you need to use Ctrl-Shift-Enter rather than just Enter. That's what Biff meant by Array formula.... HTH, Bernie MS Excel MVP "Angela" wrote in message ... Biff, What if i need the same result as below, but was using the formula =AVEDEV, =AVERAGE & =MEDIAN. Can i make it do the same thing? I tried using the IF statement you wrote below on them, and it didn't work. It does work on the =STDEV, though. Thank you in advance for all your help. You really know your formulas. Thanks :-) "Biff" wrote: I hit send before I was done! This array entered formula specifically excludes empty cells: =STDEV(IF(A1:A5<"",A1:A5)) But it returns the exact same result as: =STDEV(A1:A5) Biff "Biff" wrote in message ... Hi! Empty cells and cells that contain text are ignored. You don't need to specifically exclude empty cells. Biff "Angela" wrote in message ... Hi. I am currently using the STDEV formula. Is there a way, that i can make the formula use data from nonblank cells, only. For example: A1, A2, A5 will have numbers listed. A3 & A4 are blank. Can i get the formula to use only A1, A2, & A5? Any help would be greatly appreciated. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting-Don't use Blanks inside formula
I have blanks.
"Bernie Deitrick" wrote: Angela, Blanks are not zeroes, and Excel treats them differently. Which do you have? HTH, Bernie MS Excel MVP "Angela" wrote in message ... Bernie, Please don't hate me. But I must be doing something wrong because it isn't coming up with the same thing. Example: A1=1, B1=0, C1=2, D1=0, E1=3, D1=0, E1=4, F1:Z1=0 If using the =AVEDEV; =AVERAGE; =MEDIAN, it comes up with a different answer than if i were to use the =AVEDEV(IF(A1:Z1<"",A1:Z1)). It sounds weird, but it actually works until i enter data on the 4th cell. Am i doing something wrong? "Bernie Deitrick" wrote: Angela, All those functions ignore blanks automatically. With the =STDEV(IF(A1:A5<"",A1:A5)) method, you need to use Ctrl-Shift-Enter rather than just Enter. That's what Biff meant by Array formula.... HTH, Bernie MS Excel MVP "Angela" wrote in message ... Biff, What if i need the same result as below, but was using the formula =AVEDEV, =AVERAGE & =MEDIAN. Can i make it do the same thing? I tried using the IF statement you wrote below on them, and it didn't work. It does work on the =STDEV, though. Thank you in advance for all your help. You really know your formulas. Thanks :-) "Biff" wrote: I hit send before I was done! This array entered formula specifically excludes empty cells: =STDEV(IF(A1:A5<"",A1:A5)) But it returns the exact same result as: =STDEV(A1:A5) Biff "Biff" wrote in message ... Hi! Empty cells and cells that contain text are ignored. You don't need to specifically exclude empty cells. Biff "Angela" wrote in message ... Hi. I am currently using the STDEV formula. Is there a way, that i can make the formula use data from nonblank cells, only. For example: A1, A2, A5 will have numbers listed. A3 & A4 are blank. Can i get the formula to use only A1, A2, & A5? Any help would be greatly appreciated. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting-Don't use Blanks inside formula
Angela,
If you truly have blanks, then Excel will ignore them for all of the functions that you want to use. =AVEDEV(IF(A1:Z1<"",A1:Z1)) - Array entered using Ctrl-Shift-Enter =AVEDEV(A1:Z1) Both return the same for me, no matter how many blanks, text strings, and "" values I add in. HTH, Bernie MS Excel MVP "Angela" wrote in message ... I have blanks. "Bernie Deitrick" wrote: Angela, Blanks are not zeroes, and Excel treats them differently. Which do you have? HTH, Bernie MS Excel MVP "Angela" wrote in message ... Bernie, Please don't hate me. But I must be doing something wrong because it isn't coming up with the same thing. Example: A1=1, B1=0, C1=2, D1=0, E1=3, D1=0, E1=4, F1:Z1=0 If using the =AVEDEV; =AVERAGE; =MEDIAN, it comes up with a different answer than if i were to use the =AVEDEV(IF(A1:Z1<"",A1:Z1)). It sounds weird, but it actually works until i enter data on the 4th cell. Am i doing something wrong? "Bernie Deitrick" wrote: Angela, All those functions ignore blanks automatically. With the =STDEV(IF(A1:A5<"",A1:A5)) method, you need to use Ctrl-Shift-Enter rather than just Enter. That's what Biff meant by Array formula.... HTH, Bernie MS Excel MVP "Angela" wrote in message ... Biff, What if i need the same result as below, but was using the formula =AVEDEV, =AVERAGE & =MEDIAN. Can i make it do the same thing? I tried using the IF statement you wrote below on them, and it didn't work. It does work on the =STDEV, though. Thank you in advance for all your help. You really know your formulas. Thanks :-) "Biff" wrote: I hit send before I was done! This array entered formula specifically excludes empty cells: =STDEV(IF(A1:A5<"",A1:A5)) But it returns the exact same result as: =STDEV(A1:A5) Biff "Biff" wrote in message ... Hi! Empty cells and cells that contain text are ignored. You don't need to specifically exclude empty cells. Biff "Angela" wrote in message ... Hi. I am currently using the STDEV formula. Is there a way, that i can make the formula use data from nonblank cells, only. For example: A1, A2, A5 will have numbers listed. A3 & A4 are blank. Can i get the formula to use only A1, A2, & A5? Any help would be greatly appreciated. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting-Don't use Blanks inside formula
Bernie ~ It is working for me now. Thank you! I found out what i was doing
wrong. I was making changes to the array. If I make changes, i need to go ahead and delete the formula and just re-enter it. Then the formula you both gave me works perfectly. Thank you for all your help. :-) "Bernie Deitrick" wrote: Angela, If you truly have blanks, then Excel will ignore them for all of the functions that you want to use. =AVEDEV(IF(A1:Z1<"",A1:Z1)) - Array entered using Ctrl-Shift-Enter =AVEDEV(A1:Z1) Both return the same for me, no matter how many blanks, text strings, and "" values I add in. HTH, Bernie MS Excel MVP "Angela" wrote in message ... I have blanks. "Bernie Deitrick" wrote: Angela, Blanks are not zeroes, and Excel treats them differently. Which do you have? HTH, Bernie MS Excel MVP "Angela" wrote in message ... Bernie, Please don't hate me. But I must be doing something wrong because it isn't coming up with the same thing. Example: A1=1, B1=0, C1=2, D1=0, E1=3, D1=0, E1=4, F1:Z1=0 If using the =AVEDEV; =AVERAGE; =MEDIAN, it comes up with a different answer than if i were to use the =AVEDEV(IF(A1:Z1<"",A1:Z1)). It sounds weird, but it actually works until i enter data on the 4th cell. Am i doing something wrong? "Bernie Deitrick" wrote: Angela, All those functions ignore blanks automatically. With the =STDEV(IF(A1:A5<"",A1:A5)) method, you need to use Ctrl-Shift-Enter rather than just Enter. That's what Biff meant by Array formula.... HTH, Bernie MS Excel MVP "Angela" wrote in message ... Biff, What if i need the same result as below, but was using the formula =AVEDEV, =AVERAGE & =MEDIAN. Can i make it do the same thing? I tried using the IF statement you wrote below on them, and it didn't work. It does work on the =STDEV, though. Thank you in advance for all your help. You really know your formulas. Thanks :-) "Biff" wrote: I hit send before I was done! This array entered formula specifically excludes empty cells: =STDEV(IF(A1:A5<"",A1:A5)) But it returns the exact same result as: =STDEV(A1:A5) Biff "Biff" wrote in message ... Hi! Empty cells and cells that contain text are ignored. You don't need to specifically exclude empty cells. Biff "Angela" wrote in message ... Hi. I am currently using the STDEV formula. Is there a way, that i can make the formula use data from nonblank cells, only. For example: A1, A2, A5 will have numbers listed. A3 & A4 are blank. Can i get the formula to use only A1, A2, & A5? Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
self-updating formula | Excel Discussion (Misc queries) | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |