Calculating a Cell with a Variable

[alarm_guy1]

Hi guys I have a Cell B5 and it contains the following Text "Travel From Home to First Job =" 50
Now I have another Cell AN5 that contains the following formula "=IF(B5="holiday",0,IF(F5>17,0,IF(B5="sick",0,IF(B5="lunch",0,IF(B5="Office",0,IF(F5>17,0,IF(Q5=0,G5-X5,)))))))"

The issue is that the Cell B5 after the = the current 50 could be any value (depends where the job is) so I cant add IF(B5=""Travel From Home to First Job =50" ",0)
As my next day it could be 69 or 120 etc

Is there anyway that the variable mileage can be taken into account within a formula

many thanks in advance

Keith

[martindwilson]

to check if b5 has travel from home use
=ISNUMBER(SEARCH("travel from home",B5)) if you want to get the number use
=MID(B5,FIND("=",B5)+1,4)+0

[oeldere]

Try this one (untested, because you don't add an excel file).

"=IF(or(B5="holiday",b5="sick"",b5="lunch",b5="office",F5>17),0,IF(Q5=0,G5-X5)"

Also for your actual question you can better add an excel example, without confidentional information.

I don't see what the content of cell b5 is.

[martindwilson]

fwiw you can shorten that to
=IF(OR(B5={"holiday","sick","lunch","office"},F5>17),0,IF(Q5=0,G5-X5)

[alarm_guy1]

Hi Martin, thankyou it works fine but i need it to do something else if i attach a sheet with similar info so u can see what i'm tring to achieve, no laughing though...:-)
Basically the issue im getting is that Column B controls some of what goes on on the sheet, however we now have to put specific info in twice a day in Column B, (Travel From Home to First Job = 50) and (Travel Home from site = 45) this screws up column N by putting "False" in the column, I need Column N to be 0 (these are hidden).
there are many other hurdles to overcome but this is my current main issue as they have just redesigned this sheet (the old one worked perfectly) Most calcs are done in Columns X to AQ

Any further help greatly appreciated


Cheers

Keith

[martindwilson]

use an if
=if(ISNUMBER(SEARCH("travel from home",B5)),MID(B5,FIND("=",B5)+1,4)+0,0)

[alarm_guy1]

Many thanks, for your help, I have finally sorted my issues, your formula worked a treat, once I properly integrated it into my existing formula properly.

Once again many thanks for taking the time to assist me

Keith