Displaying a symbol based on a value being smaller then/greater then or equal to a number

[waimea]

I cross-posted this question on mrexcel.com forums.

But I am unable to post the link to the forum thread.

I am using the following formula to check if a number is higher then another number, if yes I get a ✓ and if no I get a ✕.

=IF(H29 >=L29;"✓";"✕")

I am trying to do this with the following formula:

=IF(H29<>"";IF(L29<>"";IF(H29>=L29;"✓";"✕")))

What do I need to change for the formula to display "✕" if the cell H29 is blank and/or the cell L29 is blank? And then show ✓ if the cell H29 is larger then/smaller then or equal to L29.

Also, if H29 and L29 are blank then the formula returns false, I want it to return ✕.

How can I do this so that it shows ✕ if one or both cells are blank, "" or 0. And that it only shows ✓ if both cells are not blank, "" or 0 and if H29 is bigger then/smaller or equal then L29?

In summary, I want to show two different symbols based on the value of 2 different cells.

[AliGW]

Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

[AliGW]

That’s better - thanks!

[jason.b75]

See if this works

=IF(AND(H29<>"";L29<>"";H29>=L29);"✓";"✕")

[waimea]

Thank you for your reply, it works but if H29 and / or L29 is 0 or 0,00 I get a ✓ but I want a ✕.

[jason.b75]

Do both have to be <> 0?

=IF(AND(N(H29);N(L29);H29>=L29);"✓";"✕")

[waimea]

In the best case scenario all values of H29 and L29 are not 0, empty or "".

However if H29 and / or L29 is empty I would like to get a ✕. I don't really understand the last formula but I think it's not doing what I want.



H29 L29
40 39,9
This would give me a ✓.

H29 L29
40 40
This would give me a ✓.

H29 L29
39 40
This would give me a ✕.

H29 L29
0 40
This would give me a ✕.

H29 L29
40 0
This would give me a ✕.

H29 L29
0 0
This would give me a ✕.

[jason.b75]

The formula in post 6 checks that both H29 and L29 contain a number other than 0, and that H29 is not less than L29.

If it's not doing what you want, please provide some examples where it gives the incorrect result.

[waimea]

You are correct, I though that the formula didn't update the ✓ properly but it was down to the first decimal not showing that it didn't update.

Thank you for your time and your answer! I also posted this on mrexcel, can I post your answer there?

[AliGW]

Rule 08: Cross-posting Without Telling Us

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

No further help to be offered, please, until the OP has complied with this request.

[waimea]

Do both have to be <> 0?

=IFERROR(IF(AND(N(H29);N(L29);H29>=L29);"✓";"✕");"✕")

Would it be possible to extend your formula and check if H29 is empty and if yes, then compare G29 with L29 with the same logic as above, G29 and L29 are both not null.

[AliGW]

Thank you for adding the cross-post information requested.

[jason.b75]

Try

=IFERROR(IF(AND(N(H29);N(L29);H29>=L29);"✓";IF(AND(N(G29);N(L29);G29>=L29);"✓";"✕"));"✕")

In the event that both G29 and H29 both contain numbers, H29 will be evaluated.

[waimea]

Thank you, it works beautifully!

[waimea]

Would it be possible to extend it one more time with another if clause, to check if F29 contains a value and is not empty?

[jason.b75]

Following the same logic as above

=IFERROR(IF(AND(N(H29);N(L29);H29>=L29);"✓";IF(AND(N(G29);N(L29);G29>=L29);"✓";IF(AND(N(F29);N(L29);F29>=L29);"✓";"✕")));"✕")

[waimea]

I tried following the same logic w/o a working formula.

I could be wrong but I think that this formula

if

H29 is smaller then L29

and

G29 is larger then L29 then I get a ✓

also if

H29 is larger

then L29 I get a ✓. Following the same logic?



I am not sure of my logic but I made this case picture to show when the formula doesn't work as I would like.

excel-problem.png

[jason.b75]

I think I see where I went wrong, I should have changed the logic instead of just adding to it. See if this gives the expected results.

=IFERROR(IF(AND(IF(N(L29);LOOKUP(2;1/(F29:H29>0);F29:H29)>=L29));"✓";"✕");"✕")

[waimea]

It works perfectly! Thank you.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.