If statements with a blank result

[mmode66]

I'm using an IF statement to assign a value to performance, ultimately to gather a Rank in points.
I'm using multiple criteria (about 6) and each has a point assigned based on their performance in that metric.

Issue: Some values in catagories are coming up blank. This is causing my formula to give them max points (example below: They would get "10" even though they were absent or had nothing in that column of data).

Objective: Add a calculation that will take into consideration the blank value and return "1".

Any suggestions? Below is the actual formula I'm using.

=IF(G3>=0.85,"10",IF(G3>=0.8,"9",IF(G3>=0.75,"8",IF(G3>=0.7,"7",IF(G3>=0.65,"6",IF(G3>=0.6,"5",IF(G3>=0.58,"4",IF(G3>=0.55,"3",IF(G3<0.54999,"1")))))))))

 

[KennyGreens]

I just tested out your formula on a blank cell and it returned 1. What is the value that is in cell G3 in this example?

 

[wideboydixon]

This might work better:

=IFERROR(VLOOKUP(G3,{0,1;0.55,3;0.58,4;0.6,5;0.65,6;0.7,7;0.75,8;0.8,9;0.85,10},2,TRUE),"")

WBD

 

[mmode66]

The value in the cell is Blank. I wonder what would cause mine to return a 10?

 

[mmode66]

WBD, that worked. Not sure how lol But it worked. I'll be spending some time studying that formula so i can understand it better. Thank you for teaching me something new today!

 

[dreid1011]

The value in the cell is Blank. I wonder what would cause mine to return a 10?

Is the "blank" a result of a formula as well, or is there absolutely nothing in the cell? (no formula or value) If it's a blank returned by a formula (""), then it will technically return true for all the logical tests as it's numerical value(code) is greater than .85.

 

[mmode66]

it is blank due to a formula.

 

[wideboydixon]

WBD, that worked. Not sure how lol But it worked. I'll be spending some time studying that formula so i can understand it better. Thank you for teaching me something new today!

Cool. It's just using a hard-coded VLOOKUP table. See here:

https://exceljet.net/formula/self-contained-vlookup

WBD

 

[mmode66]

Thanks. How come your formula doesn't have "" around the value as in the example in your link?

 

[jtakw]

Hi,

Don't put quote marks ( "8" ) around numbers unless it's intentional, it turns the number into TEXT, you Only want to use quote marks for Text.