Help with a forumla, IF or DIFF???

[Dr_Watson]

I'm not great with Excel 2010 but I get by.

I need to write a forumula which compares the difference between two numbers in two cells and writes a word in the third cell. The rule is B1 - A1. If that resulting number is less than -0.25 then it's "good" if it's more than -0.25 but less than 0.25 then it's "ok" and if it's more than 0.25 then it's "bad". The other problem I have is A1 is on a page called "clock" and B1 is on another page called "times" so it makes it more complicated. So far I have this forumla

=IF(('times'!B1-clock!AL36)<0.25,"good") So this works for less than 0.25 but how do I add to the forumla for between -025 to +0.25 and more than 0.25


A B C
4 3 good


A B C
4 4.1 ok (C is OK as A and B are within the limit -0.25 and +0.25)


A B C
4 4.3 bad (B1is more than 0.25 greater than A1)



My brain is wrecked. Happy new year too.

Iain

[daddylonglegs]

Hello Iain, try this version

=IF('times'!B1-clock!AL36<-0.25,"good",IF('times'!B1-clock!AL36<0.25,"ok","bad"))

[protonLeah]

never mind.

[djapigo]

Hi Iain,

You need Nested IF statements...

The IF formula actually should be 3 items... IF(condition, true, false)

The condition in your formuls is good... ('times'!B1-clock!AL36)<0.25 this will result in TRUE or FALSE...

Your true statement is right too, "good"

Now your false statement is missing, which if you only had 2 "results" then this is where the other "word" will be put (ie not meeting your condition)

But since you have 3 conditions, then a nested if is needed...

Try this...
=IF(('times'!B1-clock!AL36)<0.25,"good",IF(('times'!B1-clock!AL36)>0.25,"bad","ok"))

[FDibbins]

Hi Dr Watson, welcome to the forum (Moriarty went that-a-way by the way <--->)

you just need to nest more if() statements...
=IF('times'!B1-clock!AL36<-0.25,"good",IF(and('times'!B1-clock!AL36>=-0.25,'times'!B1-clock!AL36<0.25),"ok","bad"))

[Teethless mama]

Hi Dr Watson, welcome to the forum (Moriarty went that-a-way by the way <--->)

you just need to nest more if() statements...
=IF('times'!B1-clock!AL36<-0.25,"good",IF(and('times'!B1-clock!AL36>=-0.25,'times'!B1-clock!AL36<0.25),"ok","bad"))

It's not necessary to use AND function for this. See post #2

[FDibbins]

yes I saw that, Teethless, thanks and that is indeed a better way to go. However, I thought I would include the and(), so the OP can see how that works too

[Dr_Watson]

Thanks for the really fast replies.

reply 1

=IF('times'!B1-clock!AL36<-0.25,"good",IF('times'!B1-clock!AL36<0.25,"ok","bad"))

Rules doesn't quite work correctly. Giving me the wrong result

All other suggestions give an error, incorrect furmula or missing argument, I am double checking and can't find any mistakes.

I'm still working it, maybe I get it working shortly..it could be, the result is correct, by my calculations are wrong, let me check.

[Dr_Watson]

Working now.

Thanks for all the help, I made a small mistake in what I was calculating, should be clock-times, not times-clock.

So with that correction, I think everybody gave me a correct answer. I can now relax!

Happy New Year all.

Iain

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Dr_Watson]

Thanks FDibbins. Itīs solved and reputation is marked. I have the site saved, the replies were good and super fast so I will be back at somepoint.

Cheers

Iain