Multiplying a negative time value by a monetary rate!!

  • Thread starter Thread starter apogee
  • Start date Start date
A

apogee

hi there, I have a cell that contains a negative value, which I need t
multiply by a rate. I have set to 1904 system, what I would like to d
is if the cell contains a negative value, disregard this and treat i
as zero in the calculation. I have tried if statements, but having n
luck with it. The formula I have in the sheet is

=(DAY(O18)*24*$AM$8)+(HOUR(O18)*$AM$8)+(MINUTE(O18/60*$AM$8))-AL18

where o18 is the negative value and $am$8 is the rate. Cell o1
contains only a time value. the main problem is that this value is th
result of a formula, where the result could be either positive o
negative. is there a way to modify the above formula to basicall
disregard negative values but still calculate for positive ones.


Any help would be greatly appreciated as I am struggling big time wit
thi
 
See one answer in .misc.


Please don't post the same message to multiple groups. It just tends to
fragment your answers, and it potentially wastes the time of those
replying to a question that has already been answered in another group.


For tips on using these groups effectively, see

http://cpearson.com/excel/newposte.htm
 
=IF(O18<0,0,(DAY(O18)*24*$AM$8)+(HOUR(O18)*$AM$8)+(MINUTE(O18/60*$AM$8))-AL1
8)



--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
hi there, I have a cell that contains a negative value, which I need to
multiply by a rate. I have set to 1904 system, what I would like to do
is if the cell contains a negative value, disregard this and treat it
as zero in the calculation. I have tried if statements, but having no
luck with it. The formula I have in the sheet is

=(DAY(O18)*24*$AM$8)+(HOUR(O18)*$AM$8)+(MINUTE(O18/60*$AM$8))-AL18

where o18 is the negative value and $am$8 is the rate. Cell o18
contains only a time value. the main problem is that this value is the
result of a formula, where the result could be either positive or
negative. is there a way to modify the above formula to basically
disregard negative values but still calculate for positive ones.
Click to expand...

Times are stored as fractions of a day. So there should be no need to parse
out Day, Hour and Minute as you have.

Merely =o18*24*rate should do it.

To ignore O18 if it is negative, something like:

=(O18>0)*O18*24*rate-AL18

should do what you want.


--ron
 
Back
Top