Strings being greater than numbers (dates)

[doug firr]

I posted a question on here about an hour ago and was informed that a string always equates higher than a number in excel.

I tried it out. Cancel date is a range where, if a person has cancelled the date is present, else a "-" exists. So to count the number of people who have not cancelled on a specific date, here's a formula I just tried:

=COUNTIFS(HasPaymentInfo,"Yes",CancelDate,">"&Summary!H10) //so include everyone that has payment info="Yes" and where cancel date is either "-" or greater than the date in H10

I used this to replace the existing formula:

=COUNTIFS(HasPaymentInfo,"Yes",CancelDate,"-")-COUNTIFS(CancelDate,">"&Summary!H10) // Should do the same thing but the "new" way I learned above today requires less writing

I'd expected to see the same results. Not so. The original formula produces a count of many thousands while the new formula produces a count less than a hundred.

Just to add, 2 mins after posting I discovered this:

=SUMPRODUCT((HasPaymentInfo="Yes")*(CancelDate>Summary!H10)) // Should also produce the same but produces another number all together! It's closer to the number in the original formula but out by a hundred or so. Still, they should exact match.

Why?

 

[liveinhope]

what do you want to know when you say " count the number of people who have not cancelled on a specific date" - I'll use 14/2/14 as date to illustrate do you mean

- people who have cancelled but the cancellation was after 14/2/14

- people who have cancelled but the cancellation was on any date other than 14/2/14 (ie include cancellations before and after 14/2 but not on 14/2)


- one of the 2 above definitions + those who havent cancelled at all

 

[doug firr]

@livinhope anyone who has either not cancelled (cell contains "-") or have cancelled at a future date (Cell contains the cancellation date). So if the date in question for analysis is 14/2/14 then include all rows where CancelDate equals either "-" OR a date greater than 14/2/14, along with the additional condition hasPaymentInfo="Yes".

 

[liveinhope]

OK . looking at your 3 formulas (2 countifs ,1 sumproduct) there are 2 issues ;

First issue - assuming the cancel date dashes an actual dash not just a blank/empty cell formatted to look like a dash the countif > some other date does NOT pick up the cells with dashes . screen dump at the bottom illustrates this the first countif counts only dates > 14/2 the 2nd one correct answer countis dates > 14/2 and dashes separately . screen shot also gives the formula you need to fix

Second issue - your first and last formulas should give the same result but the middle one has a very different logic



formula # 1 =COUNTIFS(HasPaymentInfo,"Yes",CancelDate,">"&Summary!H10) ; shows those where payment info="Yes" and where cancel date greater than 14/2

formula # 2 =COUNTIFS(HasPaymentInfo,"Yes",CancelDate,"-")-COUNTIFS(CancelDate,">"&Summary!H10) ; counts records where payment info "yes" & no cancel date . then subtract recs where cancel date > 14/2 with or without payment info
formula 3 =SUMPRODUCT((HasPaymentInfo="Yes")*(CancelDate>Summary!H10)) ; agree this should be same result as formula one

name</SPAN>	cancel date</SPAN>	payinfo</SPAN>	14/02/2014</SPAN>	3</SPAN>	=COUNTIFS($B$2:$B$10,">"&$D$1,$C$2:$C$10,"yes")</SPAN>
al</SPAN>	-</SPAN>	yes</SPAN>		6</SPAN>	=COUNTIFS($B$2:$B$10,">"&$D$1,$C$2:$C$10,"yes") + COUNTIFS($B$2:$B$10,"-",$C$2:$C$10,"yes")</SPAN>
beth</SPAN>	11/02/2014</SPAN>	yes</SPAN>
cath</SPAN>	12/02/2014</SPAN>	yes</SPAN>
dan</SPAN>	-</SPAN>	yes</SPAN>
elly</SPAN>	14/02/2014</SPAN>	yes</SPAN>
frank</SPAN>	15/02/2014</SPAN>	yes</SPAN>
gail</SPAN>	16/02/2014</SPAN>	yes</SPAN>
harry</SPAN>	-</SPAN>	yes</SPAN>
inga</SPAN>	17/02/2014</SPAN>	yes</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>

 

[doug firr]

OK, but I thought that a text character always evaluates as higher than a number? Is the exception just with countifs?

 

[Jonmo1]

I posted a question on here about an hour ago and was informed that a string always equates higher than a number in excel.

The word Always is not correct here..
We should 'always' be careful when using the word 'always' lol..


It's more accurate to say
In the context of a straight greaterthan/lessthan comparison, Text is greater than numbers.
=A1>B1 = TRUE when A1 is text and B1 is any number.

However, this is not true with countif (and probably all 'functions')
It does not consider text > number

Only the straight comparison with the > < symbols directly (not in a function)

 

[Norie]

- isn't an alphanumeric character.

 

[doug firr]

@Norie OK but what does that mean?

@Jonmo1 OK but I posted this question yesterday that uses countifs greater than "-": http://www.mrexcel.com/forum/excel-questions/756228-using-countifs-nested-statement.html

 

[Jonmo1]

There's nothing in there about 'Greater Than' "-"
The formulas in that post use 'Equal to' "-"

 

[doug firr]

Sorry @Jonmo1, here is the link: http://www.mrexcel.com/forum/excel-questions/758817-using-sumproduct-nested-arrays.html