Results of formula vs real numbers

[J.W. Aldridge]

HI.

How do you change a formula to read the value as a result of a formula
and not necessarily as a real number?

Example:

In G6, i have the following formula that gives me the hour of a time
in F6

=IF(F6>0,MOD(F6-"1:00",1),"")

F6 contains 00:30:08, The result is 23

In another cell, I am using this formula, but it doesnt recognize the
23.

=SUMPRODUCT(--(B6:B58331="james"),--(G6:G58331="23"))

The 23 is actually the HOUR of time so it isn't a whole number or real
number persay. It is a rounded time to the hour.

 

[Pete_UK]

Yes, but it isn't a text value so putting quotes around it in your SP
formula won't work. Try it like this:

=SUMPRODUCT(--(B6:B58331="james"),--(G6:G58331=23/24))

as Excel stores times as fractions of a 24-hour day.

Hope this helps.

Pete

 

[Peo Sjoblom]

First of all this part

--(G6:G58331="23"))

looks for a text representation of 23, remove the quotations
if G6:G58331 have numbers. Secondly your formula

=IF(F6>0,MOD(F6-"1:00",1),"")

returns

23:30 and furthermore it is a time value so the real number is

0.979259259259259

so if you have 23 in the range you are testing for it will obviously not be
a match

If you have integers in G6:G58331 then you can use


=--TEXT(IF(F6>0,MOD(F6-"1:00",1),""),"h")




--


Regards,


Peo Sjoblom

 

[J.W. Aldridge]

Got info from both suggestions....
Working purfectly now.

Thanx!