Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula giving wrong result
Why does this:
=SUMPRODUCT(--(MONTH(B$7:B$998)=1)) return 992 when the entire column B is empty? The other 11 permtations work as one would expect, but this particular one counts 'blank' as January! Any ideas? tia -- Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula giving wrong result
Jock,
This is because Excel recognizes the fictitious date 0 Jan 1900 and is the number 0. There exists adequate reasoning behind this design choice. For the time being, you can count the Jan dates excluding blank cells with: =SUMPRODUCT((MONTH(B$7:B$998)=1)*(B$7:B$998<"")) HTH Kostis Vezerides On Oct 15, 4:48 pm, Jock wrote: Why does this: =SUMPRODUCT(--(MONTH(B$7:B$998)=1)) return 992 when the entire column B is empty? The other 11 permtations work as one would expect, but this particular one counts 'blank' as January! Any ideas? tia -- Traa Dy Liooar Jock |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula giving wrong result
Try =month(a1) when A1 is empty.
=SUMPRODUCT(--(isnumber(b$7:b$998)),--(MONTH(B$7:B$998)=1)) Jock wrote: Why does this: =SUMPRODUCT(--(MONTH(B$7:B$998)=1)) return 992 when the entire column B is empty? The other 11 permtations work as one would expect, but this particular one counts 'blank' as January! Any ideas? tia -- Traa Dy Liooar Jock -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula giving wrong result
Hi Jock
From a date viewpoint, Excel treats a blank cell as 00/01/1900 hence Month 1. Try =SUMPRODUCT(--(MONTH(B$7:B$998)=1),--(B$7:B$998<"")) -- Regards Roger Govier "Jock" wrote in message ... Why does this: =SUMPRODUCT(--(MONTH(B$7:B$998)=1)) return 992 when the entire column B is empty? The other 11 permtations work as one would expect, but this particular one counts 'blank' as January! Any ideas? tia -- Traa Dy Liooar Jock |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula giving wrong result
Thanks guys.
Next Q How do I get this: =COUNTIF($D5,(MIN($D$4:$D$15))) to ignore zero's? -- Traa Dy Liooar Jock "vezerid" wrote: Jock, This is because Excel recognizes the fictitious date 0 Jan 1900 and is the number 0. There exists adequate reasoning behind this design choice. For the time being, you can count the Jan dates excluding blank cells with: =SUMPRODUCT((MONTH(B$7:B$998)=1)*(B$7:B$998<"")) HTH Kostis Vezerides On Oct 15, 4:48 pm, Jock wrote: Why does this: =SUMPRODUCT(--(MONTH(B$7:B$998)=1)) return 992 when the entire column B is empty? The other 11 permtations work as one would expect, but this particular one counts 'blank' as January! Any ideas? tia -- Traa Dy Liooar Jock |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula giving wrong result
=($D5=MIN($D$4:$D$15))*($D5<"")
HTH Kostis On Oct 15, 5:29 pm, Jock wrote: Thanks guys. Next Q How do I get this: =COUNTIF($D5,(MIN($D$4:$D$15))) to ignore zero's? -- Traa Dy Liooar Jock "vezerid" wrote: Jock, This is because Excel recognizes the fictitious date 0 Jan 1900 and is the number 0. There exists adequate reasoning behind this design choice. For the time being, you can count the Jan dates excluding blank cells with: =SUMPRODUCT((MONTH(B$7:B$998)=1)*(B$7:B$998<"")) HTH Kostis Vezerides On Oct 15, 4:48 pm, Jock wrote: Why does this: =SUMPRODUCT(--(MONTH(B$7:B$998)=1)) return 992 when the entire column B is empty? The other 11 permtations work as one would expect, but this particular one counts 'blank' as January! Any ideas? tia -- Traa Dy Liooar Jock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula returning wrong result. | Excel Worksheet Functions | |||
Sumif formula not giving result | Excel Discussion (Misc queries) | |||
Formula not giving me the right result | Excel Worksheet Functions | |||
FORMULA PRODUCES WRONG RESULT | Excel Discussion (Misc queries) | |||
Formula Result Correct but value in the cell is wrong | Excel Worksheet Functions |