Using "if" formula when a cell contains a formula

[misterman]

Hi,

You will have to excuse me as I am a bit of a novice, but I am wondering why my IF formula isn't working - I think it's because one of the cells I am using in it has a formula. Here is a pic so I can explain a little better:
help.jpg

I have worked out the average of K2:N2 in P2, now I need to get F2 to change depending on the result of P2, on formula I tried was:

=if(p2="1","1c", if(p2="2","1b", if(p2="3","1a", if(p2="4","2c", if(p2="5","2b"....... etc

But it just keeps coming up "FALSE" in F2 and I think it's because P2 contains a formula (=o2/4)

If anyone could help it would be greatly appreciated!

[Søren Larsen]

I think that removing the " " around the numbers, =if(p2=1,"1c", if(... etc., would resolve your issue.?

When you put " " around a number, it is converted to text. And since P2 contains a number then it will always return FALSE.

[ron2k_1]

Have you tried removing the "" on the number that follows "p2=**"?

[misterman]

Yep tried that and it's still coming up "FALSE"

[Søren Larsen]

Is that not because none of your logical tests are TRUE then?

Is one of your logical tests P2=12 ?

[misterman]

I'm sorry, like I say I am a novice and I don't know what "logical test" means!

[Søren Larsen]

Does there somewhere in your formula say:

IF(P2=12, ...?

But anyway, it would be easier if you uploaded your workbook (or a sample).?

[misterman]

Sorry, yes in f2 the formula contains --> ...if(p2=12,"4a".....

[Søren Larsen]

If you can't attach your workbook, could you then supply the entire formula that is causing the problem?

[misterman]

This is the formula in F2

=if(p2=1,"1c", if(p2=2,"1b", if(p2=3,"1a", if(p2=4,"2c", if(p2=5,"2b", if(p2=6,"2a", if(p2=7,"3c"...

and it continues in the same way

[tigeravatar]

misterman,

Took a quick look at the pic you posted. The formula in P2 is =O2/4 and O2 = 49, so P2 actually equals 12.25, not 12. That is probably why the formula is returning FALSE. Try IF(INT(P2)=12,"4a",...

[misterman]

Here is my workbook. I haven't completed the formula yet but if you look at TEST2, the result in cell F2 should be 1a

[Søren Larsen]

Have a look at Tigeravatar's post; this should solve it.

Instead of using so many compound =IF() statements, you could use a lookup. This would, though, require that you make a list of values and their corresponding text you want returned.

[misterman]

Ah that's brilliant, thank you everyone for your help! I don't suppose anyone knows a quicker way of typing the formula up to ..IF(INT(P2)=24,"8a".. ?

Or is it just a case of typing it all in?

[tigeravatar]

misterman,

Attached is an example workbook based on the criteria you described. In cell P2 is this formula:
=ROUND(O2/4,0)

And in cell F2 is this formula:
=INT((P2-1)/3)+1&CHOOSE(MOD(P2-1,3)+1,"c","b","a")


That should accomplish what you're looking for

[tigeravatar]

Thought of an alternate formula for F2:
=INT((P2-1)/3)+1&MID("cba",MOD(P2-1,3)+1,1)

[misterman]

Wow that's brilliant thank you. I hate to sound ungrateful though, but the data will be entered into B2, C2, D2 and E2 as a number and a letter and my aim is to get an average of those in cell F2

so for example if I were to enter 2a in B2, 2a in C2, 2a in D2, 2a in E2 then in F2 I would like it to find the average (Which in this case would, again, be 2a

am I going about it the wrong way? Can you suggest a formula?

[tigeravatar]

misterman,

It's sorta ugly, but this should do it. In cell F2:

Please Login or Register  to view this content.

[misterman]

That works brilliantly! Thank you for taking the time to write it for me, I seriously can't thank you enough, if you were in England I would buy you a beer!

Thanks again

[tigeravatar]

You're very welcome