Results of formula vs real numbers

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

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.
 
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
 
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
 
Back
Top