Nested IF statement
I have the following values in cells C5, D5 and E5:
1.21, 1.15, 1.17
I want to compare ALL of these to the value in cell H5 which is 1.06.
If they are all greater than 1.06 then cell J5 shall indicate "Pass", otherwise, "Fail".
I used the following with no success.
=IF(C5:E5>H5,"pass","fail")
Try:
=IF(COUNTIF(C5:E5,">"&H5)=3,"pass","fail")
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
=if(And(C5>H5,D5>H5,E5>H5),"Pass","Fail")
... Or what NBVC said... I like his better...
I '<3' reputation. If I helped, click the scales. This will be helping an internal departmental competition with co-workers.
Reputation can be granted through the Scales Icon (for classic layout), or the little Star on the bottom of the post next to the blogging function for the new forum layout.
If you're not busy, and really feel down on life, read my excel blog which may or may not have contents.
Thank you.
What if I want to go a step further.
I now want to compare those values to both cells H5=1.06 and cell H6=1.35. That is, I want "pass" if ALL of the values in C5 to E5 are between 1.06 and 1.31, and "fail' otherwise.
I'll leverage HBVS's equation:
=IF(AND(COUNTIF(C5:E5,">"&H5)=3,COUNTIF(C5:E5,"<"&H6)=3),"pass","fail")
I '<3' reputation. If I helped, click the scales. This will be helping an internal departmental competition with co-workers.
Reputation can be granted through the Scales Icon (for classic layout), or the little Star on the bottom of the post next to the blogging function for the new forum layout.
If you're not busy, and really feel down on life, read my excel blog which may or may not have contents.
Try:
=IF((COUNTIF(C5:E5,">="&H5)-COUNTIF(C5:E5,">"&H6))=3,"pass","fail")
This includes the limits set in H5 and H6.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Wow, I've never seen my handle spelled that badly offOriginally Posted by Miraun
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Wow... that was pretty impressively bad... Sorry about that. I'll just copy/paste all usernames for a bit... and it was only 4 characters
I '<3' reputation. If I helped, click the scales. This will be helping an internal departmental competition with co-workers.
Reputation can be granted through the Scales Icon (for classic layout), or the little Star on the bottom of the post next to the blogging function for the new forum layout.
If you're not busy, and really feel down on life, read my excel blog which may or may not have contents.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks