Conditional Formatting to multiply a cell

[nkitchen31]

Trying to figure out how to multiply a cell by 2 if a different cell has an "x" or any character for that matter.

Example-- I want L2 to take the value of F2, add the value of G2*1.5 and then if H2 had an "x", then multiply F2 by itself.

Thanks in advance!

[JBeaucaire]

Maybe:

=F2+(G2*1.5)+((LEN(H2)>0)*F2)

[nkitchen31]

Okay, that at least gives me a value... the wrong value, but a value nonetheless... it's giving me 122:52

it should be like 19:21 or something similar. Any ideas? Thanks!

Edit: I see the problem is in the G column... it's getting confused somehow.

[JBeaucaire]

How did you get 19:21?

[Jonmo1]

Is this related to your other thread ?

http://www.excelforum.com/excel-form...ding-time.html

[nkitchen31]

Jonmo1: Yes it is...

Excel is getting confused because it think's its a day and time, and not hours and minutes.

Well, if I put "3" under Days MUP it should take F2 and add (3*1.5) and get 4.5+7:26 which is 11:56... or if there is an X in the JUM column then it should be 7:26 * 2 which is 14:52... but if I have an X in the JUM column and 3 in the Days MUP Column, it would be:

(7:26*2)+(3*1.5) which is 19 and change...

[Jonmo1]

You can combine the solution I provided in the other thread, with what JBeaucaire posted

=F2+(G2*1.5/24)+((LEN(H2)>0)*F2)

[nkitchen31]

OH MY GOSH thank you so much!!! That's perfect!

[nkitchen31]

Maybe I need another thread, but I have another question... If I have an "X" in TXR, SIC, or VAC column, I want to take the value of the respective F cell and keep a total in row 16. Does that make sense?

Like, if I have an X under VAC for K4 and K5, I want K16 to be F4+F5.

Thanks again guys!

[Jonmo1]

You're welcome.


FYI, the basic problem you've had is nut fully understanding how Excel handles Dates and Times.

DATES are whole numbers, incrimenting by 1 since Jan 1 1900
1 = Jan 1 1900
32 = Feb 1 1900
etc
42556 = Jul 5 2016

TIME is the Decimal value of a number. A FRACTION of 1 day.
1 hour = 1/24 or 0.041667
6 AM is 6 hours, or 6/24, or 0.25

So when you had the whole number 6 in G6, it was adding 6 DAYS (144 hours, 6*24), not 6 Hours.

[Jonmo1]

Try

=SUMIF(K$2:K$14,"X",$F$2:$F$14)

[nkitchen31]

Ahhhh I see now... thanks for the explanation.

And, brilliant! Works like a charm. Truly time savers guys, thanks again.