Coming soon - Get a detailed view of why an account is flagged as spam!
view details

This post has been de-listed

It is no longer included in search results and normal feeds (front page, hot posts, subreddit posts, etc). It remains visible only via the author's post history.

1
Including "N/A" text as an option for IF formula using values
Post Flair (click to view more posts with a particular flair)
Post Body

Sorry if the title isn't clear, I'm not sure how to explain as I don't really know the terms for things but I've been trying to learn excel for a bit.

The current situation I have is that I want a column of cells to return 1 of 4 results based on numerical values, and I figured out how to return 3/4 of the images as intended, however I realized that inputting N/A returns one of those 3 images and I'm not sure how to circumvent it.

I'm making a sheet to organize things for my guild in an online game, and the formula I've got so far is

=IF(M2 >= 60, sheet1!$A$3, IF( L2 >= 40, sheet1!$A$2, IF(J2 >= 2, sheet1!$A$1)))

Essentially, in descending order of hardest content someone can contribute to to easiest in terms of progress. It references multiple cells because the game we play has different metrics to measure a player's contribution to a specific fight so it's impossible to unify it to one value or reference cell given the current community norms.

Is there any way I can include an IF(J2:M2 = N/A, sheet1!$A$5 that doesn't break the formula? I've been banging my head against it and googling and haven't figured anything out.

Currently, if someone puts N/A in the M column, it will satisfy the condition of >= 60, for whatever reason, and appear as though they are capable of doing the boss even though it should be the opposite.

As such I'd also need to input it in a way that the N/A image result only appears if none of the 3 other conditions are met, but that it also blocks from the strongest boss to appear if someone doesn't satisfy the conditions. If it helps, there is no possible way for any of the reference values to currently surpass 999, or any higher values if that can help.

Additionally, if there's an easier way than this formula to figure it out, any help would be appreciated. I hope ANY of this makes sense

I know this may not be the most straightforward request but I'm not sure how to otherwise explain it.

With the help from another thread, I ended up with the formula which does everything exactly as I needed it to :)

=IF(and(M2 <> "N/A", M2 >= 60), sheet1!$A$3, IF(and(L2 <> "N/A", L2 >= 40), sheet1!$A$2, IF(and(J2 <> "N/A", J2 >= 2), sheet1!$A$1, IF(ISNUMBER(J2), sheet1!$A$6, IF(EXACT(J2, "N/A"), sheet1!$A$5, "")))))

Author
Account Strength
90%
Account Age
8 years
Verified Email
Yes
Verified Flair
No
Total Karma
2,999
Link Karma
338
Comment Karma
2,641
Profile updated: 3 days ago
Posts updated: 8 months ago

Subreddit

Post Details

We try to extract some basic information from the post title. This is not always successful or accurate, please use your best judgement and compare these values to the post title and body for confirmation.
Posted
1 year ago