| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula returnsas "1" even with nothing to solve for?
-- Question-- I used the count function to determine the number of time check dates go beyond term limit. I have not even encoded check dates already cell returns "1"? "=COUNT(P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+ (AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+ (AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+ (BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+ (BX2-H2J2)+(CA2-H2J2)+(CD2-H265)+(CG2-H2J2)" Whe H2 is invoice date J2 is term limit joromajr |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula returnsas "1" even with nothing to solve for?
Did you miss a couple of ()'s when you typed this post?
I guessing your formula looks more like: =COUNT((P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+ (AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+ (AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+ (BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+ (BX2-H2J2)+(CA2-H2J2)+(CD2-H265)+(CG2-H2J2)) All those expressions are returning numbers--but you're adding them into a single number. =count(1+0+1+0+1+1+1) returns 1. I'm guessing that you don't want the equivalent of: =count(1,0,1,0,1,1,1) since it would count the 0's as well as the 1's. I think you'll want to drop the =count() function and just add those numbers that evaluate to true/false (1's/0's). (P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+ (AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+ (AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+ (BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+ (BX2-H2J2)+(CA2-H2J2)+(CD2-H265)+(CG2-H2J2) ps. Why is this in your formula: +(CD2-H265)+ It looks out of place. joromajr wrote: -- Question-- I used the count function to determine the number of time check dates go beyond term limit. I have not even encoded check dates already cell returns "1"? "=COUNT(P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+ (AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+ (AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+ (BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+ (BX2-H2J2)+(CA2-H2J2)+(CD2-H265)+(CG2-H2J2)" Whe H2 is invoice date J2 is term limit joromajr -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula returnsas "1" even with nothing to solve for?
-- follow up-- Thanks for the quick reply. I did as you said but still the "1" appears and yes CD2 is part of the solution. Have other suggestions, coz I really need them. joromajr "Dave Peterson" wrote: Did you miss a couple of ()'s when you typed this post? I guessing your formula looks more like: =COUNT((P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+ (AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+ (AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+ (BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+ (BX2-H2J2)+(CA2-H2J2)+(CD2-H265)+(CG2-H2J2)) All those expressions are returning numbers--but you're adding them into a single number. =count(1+0+1+0+1+1+1) returns 1. I'm guessing that you don't want the equivalent of: =count(1,0,1,0,1,1,1) since it would count the 0's as well as the 1's. I think you'll want to drop the =count() function and just add those numbers that evaluate to true/false (1's/0's). (P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+ (AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+ (AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+ (BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+ (BX2-H2J2)+(CA2-H2J2)+(CD2-H265)+(CG2-H2J2) ps. Why is this in your formula: +(CD2-H265)+ It looks out of place. joromajr wrote: -- Question-- I used the count function to determine the number of time check dates go beyond term limit. I have not even encoded check dates already cell returns "1"? "=COUNT(P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+ (AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+ (AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+ (BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+ (BX2-H2J2)+(CA2-H2J2)+(CD2-H265)+(CG2-H2J2)" Whe H2 is invoice date J2 is term limit joromajr -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula returnsas "1" even with nothing to solve for?
Maybe each of those addends should be more like:
((p2-h2)j2) Was 65 part of the solution? joromajr wrote: -- follow up-- Thanks for the quick reply. I did as you said but still the "1" appears and yes CD2 is part of the solution. Have other suggestions, coz I really need them. joromajr "Dave Peterson" wrote: Did you miss a couple of ()'s when you typed this post? I guessing your formula looks more like: =COUNT((P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+ (AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+ (AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+ (BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+ (BX2-H2J2)+(CA2-H2J2)+(CD2-H265)+(CG2-H2J2)) All those expressions are returning numbers--but you're adding them into a single number. =count(1+0+1+0+1+1+1) returns 1. I'm guessing that you don't want the equivalent of: =count(1,0,1,0,1,1,1) since it would count the 0's as well as the 1's. I think you'll want to drop the =count() function and just add those numbers that evaluate to true/false (1's/0's). (P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+ (AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+ (AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+ (BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+ (BX2-H2J2)+(CA2-H2J2)+(CD2-H265)+(CG2-H2J2) ps. Why is this in your formula: +(CD2-H265)+ It looks out of place. joromajr wrote: -- Question-- I used the count function to determine the number of time check dates go beyond term limit. I have not even encoded check dates already cell returns "1"? "=COUNT(P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+ (AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+ (AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+ (BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+ (BX2-H2J2)+(CA2-H2J2)+(CD2-H265)+(CG2-H2J2)" Whe H2 is invoice date J2 is term limit joromajr -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula returnsas "1" even with nothing to solve for?
Exactly what did you do? If you used this...
=(P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+(AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+(AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+(BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+(BX2-H2J2)+(CA2-H2J2)+(CD2-H2J2)+(CG2-H2J2) as Dave suggested, it works fine (note I changed the next-to-last logical expression to use J2 rather than 65 as it seemed highly unlikely that, given the structure of the formula, that one element should be different in that way. Rick "joromajr" wrote in message ... -- follow up-- Thanks for the quick reply. I did as you said but still the "1" appears and yes CD2 is part of the solution. Have other suggestions, coz I really need them. joromajr "Dave Peterson" wrote: Did you miss a couple of ()'s when you typed this post? I guessing your formula looks more like: =COUNT((P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+ (AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+ (AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+ (BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+ (BX2-H2J2)+(CA2-H2J2)+(CD2-H265)+(CG2-H2J2)) All those expressions are returning numbers--but you're adding them into a single number. =count(1+0+1+0+1+1+1) returns 1. I'm guessing that you don't want the equivalent of: =count(1,0,1,0,1,1,1) since it would count the 0's as well as the 1's. I think you'll want to drop the =count() function and just add those numbers that evaluate to true/false (1's/0's). (P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+ (AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+ (AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+ (BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+ (BX2-H2J2)+(CA2-H2J2)+(CD2-H265)+(CG2-H2J2) ps. Why is this in your formula: +(CD2-H265)+ It looks out of place. joromajr wrote: -- Question-- I used the count function to determine the number of time check dates go beyond term limit. I have not even encoded check dates already cell returns "1"? "=COUNT(P2-H2J2)+(S2-H2J2)+(V2-H2J2)+(Y2-H2J2)+(AB2-H2J2)+ (AE2-H2J2)+(AH2-H2J2)+(AK2-H2J2)+(AN2-H2J2)+(AQ2-H2J2)+ (AT2-H2J2)+(AW2-H2J2)+(AZ2-H2J2)+(BC2-H2J2)+(BF2-H2J2)+ (BI2-H2J2)+(BL2-H2J2)+(BO2-H2J2)+(BR2-H2J2)+(BU2-H2J2)+ (BX2-H2J2)+(CA2-H2J2)+(CD2-H265)+(CG2-H2J2)" Whe H2 is invoice date J2 is term limit joromajr -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "too many different cell formats". How to solve this problem? | Excel Discussion (Misc queries) | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| How do I solve "too many different cell formats" in Excel 2003 | Excel Discussion (Misc queries) | |||
Linear Mode
