MS Excel formula question

[SC03OTT]

having a bit of a metal block.

right, i want to display the word PASS if the number entered is less than 24.999, but display the word FAIL if 25.0 or higher is entered, AND if NO READING is entered.

make sense?

cheers!!

 

[Michael_L]

try:

=IF(A10<25,"PASS",IF(A10>25,"FAIL",IF(A10=0,FAIL)))


next to the mark.


The cell ref in this case is A10 and can be changed.

 

[SC03OTT]

cheers for that. i came up with this though, and it works

=IF(A1<24.999,"PASS",IF(A1>=25,"FAIL",IF(A1=NO READING,"FAIL")))

 

[MarkCup]

cheers for that. i came up with this though, and it works

=IF(A1<24.999,"PASS",IF(A1>=25,"FAIL",IF(A1=NO READING,"FAIL")))

Haven't come across that before...that expression genuinely works?

 

[MarkCup]

Just realised as well that that formula won't work if you put in 24.99.

Try...

=IF(A1<25,"PASS",IF(A1>=25,"FAIL",IF(A1=NO READING,"FAIL")))

 

[SC03OTT]

mine works if i type 24.99, but not 24.999. but, as my test equipment only has 2 digits after the decimal point, it does not matter.

and yes it does honestly work lol.

 

[MarkCup]

Ah, yeah, I meant 24.999.

I like the NO READING thing though.

 

[Rhino172]

you don't need to nter no reading tho...you could just do...

=IF(A1<25,"PASS",IF(A1>=25,"FAIL","FAIL"))

the commer i've highlighted meaning "if not"

 

[Marky_]

As that seem to have been answered, i'd like to hijack the thread and ask if it's possible to format the text colour in a certain cell, if the number in it is higher/lower than a set number? i.e green >=5 and red <5.

 

[Rhino172]

yup...

conditional formatting. real easy but can't remember what menu it's under...

hit F1 for the help menu and look conditional formatting and away you go

 

[Marky_]

That rings a bell. Thought it was something simple but i couldn't remember back to gcse IT

 

[MarkCup]

It's in the format menu, you can have up to three different rules and formats per cell.

 

[SC03OTT]

you don't need to nter no reading tho...you could just do...

=IF(A1<25,"PASS",IF(A1>=25,"FAIL","FAIL"))

the commer i've highlighted meaning "if not"

ah right cool. for the purposes of my tests though, 'NO READING' does have to be entered if..well, if no reading is given.

 

[Marky_]

Thanks Mark and Rhino :approve: