Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
is < or together in same formula
I need to do a formula and have had no luck.
Need to do a countif for b17:b26 but I also need to in that list if there are numbers <4.4 and 4.0 Any formula I have tried does not work. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
is < or together in same formula
try
=sumproduct((b17:b264)*(b17:b26<4.4)) -- Don Guillett SalesAid Software "sgreene" wrote in message ... I need to do a formula and have had no luck. Need to do a countif for b17:b26 but I also need to in that list if there are numbers <4.4 and 4.0 Any formula I have tried does not work. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
is < or together in same formula
=SUMPRODUCT(--(b17:b264.0),--(b17:b26<4.4))
-- Allllen "sgreene" wrote: I need to do a formula and have had no luck. Need to do a countif for b17:b26 but I also need to in that list if there are numbers <4.4 and 4.0 Any formula I have tried does not work. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
is < or together in same formula
=COUNTIF(B17:B264)-COUNTIF(B17:B26=4.4)
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "sgreene" wrote in message ... I need to do a formula and have had no luck. Need to do a countif for b17:b26 but I also need to in that list if there are numbers <4.4 and 4.0 Any formula I have tried does not work. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
is < or together in same formula
Allllen,
Tried your formula...4 rows of numbers on top are what they should be...4 numbers on the bottom are what they come out with your formula. Some are correct and some are not??? Can you suggest anything. I truly appreciate your help. 4 5 0 0 10 3 6 3 3 0 0 5 0 2 4 2 0 2 0 0 3 8 0 0 4 5 0 0 10 3 4 3 1 0 0 1 0 2 4 2 0 2 0 0 3 8 0 0 "Allllen" wrote: =SUMPRODUCT(--(b17:b264.0),--(b17:b26<4.4)) -- Allllen "sgreene" wrote: I need to do a formula and have had no luck. Need to do a countif for b17:b26 but I also need to in that list if there are numbers <4.4 and 4.0 Any formula I have tried does not work. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
is < or together in same formula
All formulas work on your original request
-- Don Guillett SalesAid Software "sgreene" wrote in message ... Allllen, Tried your formula...4 rows of numbers on top are what they should be...4 numbers on the bottom are what they come out with your formula. Some are correct and some are not??? Can you suggest anything. I truly appreciate your help. 4 5 0 0 10 3 6 3 3 0 0 5 0 2 4 2 0 2 0 0 3 8 0 0 4 5 0 0 10 3 4 3 1 0 0 1 0 2 4 2 0 2 0 0 3 8 0 0 "Allllen" wrote: =SUMPRODUCT(--(b17:b264.0),--(b17:b26<4.4)) -- Allllen "sgreene" wrote: I need to do a formula and have had no luck. Need to do a countif for b17:b26 but I also need to in that list if there are numbers <4.4 and 4.0 Any formula I have tried does not work. Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
is < or together in same formula
Bob, thank you, but couldn't get it to work at all.
"Bob Phillips" wrote: =COUNTIF(B17:B264)-COUNTIF(B17:B26=4.4) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "sgreene" wrote in message ... I need to do a formula and have had no luck. Need to do a countif for b17:b26 but I also need to in that list if there are numbers <4.4 and 4.0 Any formula I have tried does not work. Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
is < or together in same formula
You need to clearly restate your request along with examples.
-- Don Guillett SalesAid Software "sgreene" wrote in message ... Bob, thank you, but couldn't get it to work at all. "Bob Phillips" wrote: =COUNTIF(B17:B264)-COUNTIF(B17:B26=4.4) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "sgreene" wrote in message ... I need to do a formula and have had no luck. Need to do a countif for b17:b26 but I also need to in that list if there are numbers <4.4 and 4.0 Any formula I have tried does not work. Thanks |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
is < or together in same formula
sgreene wrote:
"Bob Phillips" wrote: =COUNTIF(B17:B264)-COUNTIF(B17:B26=4.4) Bob, thank you, but couldn't get it to work at all. You are correct. Bob's formula has some serious syntax errors. Try: =COUNTIF(B17:B26, "4") - COUNTIF(B17:B26, "=4.4") However, I believe that should have the same results as Don's SUMPRODUCT formulation. You already said Don's formula did not always produce the results you expect. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
is < or together in same formula
sgreene wrote:
Tried your formula...4 rows of numbers on top are what they should be...4 numbers on the bottom are what they come out with your formula. Some are correct and some are not??? Can you suggest anything. It would be helpful if you posted the contents of the cells of one or two ranges that did not sum as you expected. Two possible explanations come to mind: 1. You misstated your conditons, and you are really interested in =4 and/or <=4.4. When you summed by hand, you subconsciously included those numbers that are equal to one or both ends of the range. But be wary of testing for equality in Excel. It is subject to misinterpretation due to the difference between displayed and actual values (e.g., 3.99 might appear as "4.0"). 2. Some of the cells that you intend to contain numbers are formatted as Text. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
is < or together in same formula
Hi Joe,
Tried your formula, but got a negative result in some of the columns " wrote: sgreene wrote: "Bob Phillips" wrote: =COUNTIF(B17:B264)-COUNTIF(B17:B26=4.4) Bob, thank you, but couldn't get it to work at all. You are correct. Bob's formula has some serious syntax errors. Try: =COUNTIF(B17:B26, "4") - COUNTIF(B17:B26, "=4.4") However, I believe that should have the same results as Don's SUMPRODUCT formulation. You already said Don's formula did not always produce the results you expect. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
is < or together in same formula
sgreene wrote:
" wrote: Bob's formula has some serious syntax errors. Try: =COUNTIF(B17:B26, "4") - COUNTIF(B17:B26, "=4.4") Tried your formula, but got a negative result in some of the columns First, it is Bob's formula, not mine. I merely corrected Bob's typos. Second, the fact that you got some negative results suggests that there is something very wrong with the data in your range(s). The only way for that formula to have a negative result (if it is entered correctly) is for the range to have some numbers =4.4, but no numbers 4. I hope you can see that that is a mathematical impossibility. At this point, I think any further discussion is impossible without your posting a concrete example, complete with formulas used, as Don suggested. But please be sure to cut-and-paste. The root cause of your problems might be some typos. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
is < or together in same formula
Technical Errata....
I wrote: =COUNTIF(B17:B26, "4") - COUNTIF(B17:B26, "=4.4") [....] The only way for that formula to have a negative result (if it is entered correctly) is for the range to have some numbers =4.4, but no numbers 4. I hope you can see that that is a mathematical impossibility. I should have said: the only way ... is ... to have __more__ numbers =4.4 than numbers 4. But that is just a nitpick. It is the same mathematical impossibility. The point is: every number =4.4 is also 4. The count of numbers 4 is always greater than or equal to the count of numbers =4.4. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |