Formula for

[Guest]

Hi.

I want a formula to enter into a cell - if A1 is "M" then I want C1 to be
£8.00 or
if A1 is "C or V" then I want C1 to be £2.00. Is there a formula that I can
enter in Cell C1 to make display either £2.00 or £8.00 depending on wat is in
A1 -

Thanks for any help

Andy JL

 

[bernard liengme]

Use =IF(A1="M",8,IF(OR(A1="C",A1="V"),2,""))
If A1 is not M,C or V you get a blank
Use =IF(A1="M",8,IF(OR(A1="C",A1="V"),2,0))
If A1 is not M,C or V you get a zero
Not case sensitive
best wishes
Bernard

 

[Guest]

Try this:

C1: IF(A1="M",8,IF(OR(A1="C",A1="V"),2,"Invalid Entry")

Also, if you aren't already, you might want to use Data Validation on Cell
A1 to only allow M,C, or V.

Does that help?

••••••••••
Regards,
Ron

 

[Guest]

Ron,

Thank you very much for that prompt reply- it works very well. Now how could
I quickly apply the same formula down the complete column - ie A1 to A15 for
example with the result going in the corresponding row in column C.1-15

Thank you again for your previous reply

Andy JL

 

[bernard liengme]

If you copy IF(A1="M",8,IF(OR(A1="C",A1="V"),2,"Invalid Entry")
down to C2 it will automatically change to
IF(A2="M",8,IF(OR(A2="C",A2="V"),2,"Invalid Entry")

does that answer question?
Bernard

 

[Guest]

Bernard,
No, what I'm trying to do is apply the same formula that applies to row 1
down to row 15 for example -
I.e if A1 is M the C1 is 8
if A2 is M then C2 is * and so on.
I tried to copy and paste the formula from C1 to cell C2 but it did not
change.

Andy JL

 

[George Gee]

Andy


I think Bernard is trying to get you to think for yourself here.

Select cell C1, move your cursor to the lower right of the cell.
it will become a black cross, left-click and drag down to cell C15.

The formulas should change to reflect the row number.

George Gee

 

[Guest]

George,

Thank you very much for that - I am very new to Excel and thus I would never
have work that out unless you had helped me. You may have ralised that I'm
new to all this by the number of posts that I have posted.One more bit of
useful info I have gained.

Thanks again

 

[George Gee]

Andy

Ron's formula contains a couple of errors.
It should be:
=IF(A1="M",8,IF(OR(A1="C",A1="V"),2,"Invalid Entry"))

Must begin with an equals sign, and note the extra bracket on the end.

Try that in cell C1, and copy down to cell C15 as I have just posted.

George Gee

 

[George Gee]

Andy

The regular posters here are all geniuses, and tend to forget
that this newsgroup is for people who actualy *are* new users!

Happy to assist.

George Gee

 

[Guest]

Thanks for correcting that, George...
I inadvertently left off the first and last characters when copy/pasting.
I'll have to pay better attention. :\


***********
Regards,
Ron

 

[George Gee]

Ron

No problem.

George Gee

Thanks for correcting that, George...
I inadvertently left off the first and last characters when
copy/pasting. I'll have to pay better attention. :\


***********
Regards,
Ron

 

[Guest]

Bernard,
To your formula below I want to add if A1=D then it = the word "DAY", I have
been trying allways but can not get it to enter the word"DAY" even if i cut
the formula down to If (A1="D",DAY ....... I can do it with a number instead
of the "DAY" but I can not put it in to you formula. I think I have
expalinded myself!!!

Thanbk you

Andy JL

 

[Adam Kroger]

Put " marks around DAY

If (A1="D","DAY", <whatever>)

Adam

 

[Guest]

Adam,

Been trying most the afternoon without success , how do I Put -
IF(A1="D","DAY", into =IF(A1="M",8,IF(OR(A1="C",A1="V"),2,""))

I'm getting a very sore head and the wall is not looking so good either!!!!!!!

Thank You

Andy JL

 

[Roger Govier]

Hi Andy

Try=IF(A1="D","DAY",IF(A1="M",8,IF(OR(A1="C",A1="V"),2,"")))
I know a good plasterer!!!<bg>


Regards

Roger Govier

 

[Guest]

Roger,

Thank you for that - works very well thank you, sorry for the delay in
replying ,

Can you assist me with this - I currently have worked out a formula -
=IF(C24="-",100,IF(OR(C24="C",C24="M",C24="V"),D24*E24)) this I'm putting in
Column F, but when there is nothing in Columns C,D,or E i get the word FALSE
in the column F row, can you shed some light on this, Have tried putting ,"0"
and "" etc at the end but withour any joy.
I( would be greatful of any help PLEASE.

Andy JL

 

[George Gee]

Andy

What do you want F24 to display, when C,D & E are blank?

Try: =IF(C24="-",100,IF(OR(C24="C",C24="M",C24="V"),D24*E24,""))

George Gee

 

[Roger Govier]

Hi Andy

One way
=IF(ISBLANK(C24),"",IF(C24="-",100,IF(OR(C24="C",C24="M",C24="V"),D24*E24)))

This will return nothing if C24 is empty,
if c24 is something but not any of the tested values, but D24 and E24 are
blank, then it would return 0

Regards

Roger Govier

 

[Guest]

Thank you one and all for all the help that you have given me over the last
few days, as a result I have gained some very useful knowledge of formulas,
and I gave an all siging and dancing Invoice for my sons carpet fitting work.

Thanks agian,
Andy JL