IF Statement

[dcjones]

Hi All,

I am not to good with excel but I am learning fast.

Problem, I have a worksheet, on the sheet there are 3 col's.

Col1 could contain the word "home"
Col2 contains mileage.

What I want to do is:-

If COL1 contains the word "home" then COL2 contains the amount of miles
LESS 8 miles.

Can anyone point me in the right direction to create a formula,

Many thanks in advance.

Happy new year to you all at 24:00 01/01/05-06

Regards

Dereck

 

[Dave Peterson]

Something like:

=if(a1="home",b1-8,"what goes here instead")

And drag down your column.

 

[wjohnson]

Example A6=Home
B6=40
C6=32
=IF(A6="home",B6-8,"Check")
The "Check" is whatever you want to have for the "FALSE" part of th
statement. You can also add another "IF" condition: The followin
statement
is the same as the above for the 1st IF - the 2nd IF is adding 8 i
A6=Work.
=IF(A6="home",B6-8,IF(A6="work",B6+8,"Check")).
You can have upto 7 "IF" Statements in on formula

 

[dcjones]

Hi both and thanks for your replies.

Just to run this by you again so I fully understand it (thick day)

Cell L5 contains "home"
Cell S5 contains "10" (for testing)
Cell AE5 has the following formula behind it:-

=IF(L5="home",S5-8,"Check") This is where I do not understand it
(sorry)

=IF(L5="home" and S5= "any number" then AE5 should = S5 - 8)

Does that make sense, I know its not written as a formula (again
sorry)

Any help would be great.

Many thanks, keep safe and well.

Dereck

 

[darkwood]

What he means by "check" is that as you are writing an "IF" statement,
you have to say what you want the value to be if true, and what if is
it false. So if you say L5 = home and S5 = 10 then you are correct
with the formula as it is written. However, if L5 contains something
besides "home", you have to indicate in the formula what you want
returned as well. If you don't want anything, and want it to be blank,
just enter two sets of quotes.

so:

=IF(L5="home",S5-8,"") for a blank returned.

However, since you have 'home' listed, I assume you have another
location as well that you don't want to apply the (-8) to, and just
want it to equal the mileage field without any subtraction you'd use
this:

=IF(L5="HOME",S5-8,S5)

HTH

-Scott