New to excel and learning, need guidance.

[HPGiroux]

Hi everyone!

I thought I had found the right formula, but I clearly have not. I'm looking for the following: If H5 (% result) is equal to or greater than 80% and equal to or less than 84%, give it 5 points, If H5 is equal to or greater than 85% and equal to or less than 89%, give it 15 points and if it's equal to or greater than 90%, give it 20 points. My current formula just reads false no matter the result in H5. Could you please tell me where I'm going wrong? or what formula to look at?

Current formula: =IF(AND(H5>=0.8, M7<=0.84), "5",IF(AND(H5>=0.85, H5<=0.89), "15",IF(H5>=0.9,"20")))

Thank you in advance for any help!

Sincerely,

Hollie

 

[Joe4]

Welcome to the Board!

What exactly do the values in H5 look like?

Are they entered as Text or Numbers?

What does this formula return?

=ISNUMBER(H5)

Also, if you want to return the numeric values of 5, 15, and 20 and NOT text values, remove the double-quotes from around those numbers in your formula.

 

[HPGiroux]

You know... I'm silly. I just found what I did and it wasn't until I reread my post... Lol. It's working now, but if there is a better way, please let me know!

=IF(AND(H5>=0.8, H5<=0.84), "5",IF(AND(H5>=0.85, H5<=0.89), "15",IF(H5>=0.9,"20"))) ... this works

H5 formula is =SUM(F5/E5) (in image)

=ISNUMBER(H5) comes back as True

 

[HPGiroux]

Welcome to the Board!

What exactly do the values in H5 look like?

Are they entered as Text or Numbers?

What does this formula return?

=ISNUMBER(H5)

Also, if you want to return the numeric values of 5, 15, and 20 and NOT text values, remove the double-quotes from around those numbers in your formula.

I spoke too soon. It works until my result is 85% or more then it doesn't give it the value of 15, it gives 5

 

[HPGiroux]

I spoke too soon. It works until my result is 85% or more then it doesn't give it the value of 15, it gives 5

=IF(AND(H5>=0.8,H7<=0.84),5,IF(AND(H5=0.85,H5<=0.89),15,IF(H5>=0.9,20)))

 

[Joe4]

H5 formula is =SUM(F5/E5)

Just use:

=F5/E5

The SUM portion of this formula is doing nothing, and can be removed. You aren't adding anything.

Also note that in your original formula, you have not told it what to return if none of your conditions are met, which is why it returns FALSE.
If you want to return nothing if no conditions are met, you can use:

=IF(AND(H5>=0.8,H7<=0.84),5,IF(AND(H5=0.85,H5<=0.89),15,IF(H5>=0.9,20,"")))

Are you still having any issues?

 

[HPGiroux]

Just use:

=F5/E5

The SUM portion of this formula is doing nothing, and can be removed. You aren't adding anything.

Also note that in your original formula, you have not told it what to return if none of your conditions are met, which is why it returns FALSE.
If you want to return nothing if no conditions are met, you can use:

=IF(AND(H5>=0.8,H7<=0.84),5,IF(AND(H5=0.85,H5<=0.89),15,IF(H5>=0.9,20,"")))

Are you still having any issues?

Thank you! I entered the formula and it's currently giving false even though H5 is 85%

=IF(AND(H5>=0.8,H5<=0.84),5,IF(AND(H5>=0.85,H5<=0.89),15,IF(H5>=0.9,20,"FALSE")))

 

[Joe4]

It is almost certainly due to rounding. Even though you may only show two decimals, Excel remembers all of them.
Try changing your formula from:

=F5/E5

to:

=ROUND(F5/E5,2)

and I think it will take care of your issue.

 

[HPGiroux]

It is almost certainly due to rounding. Even though you may only show two decimals, Excel remembers all of them.
Try changing your formula from:

=F5/E5

to:

=ROUND(F5/E5,2)

and I think it will take care of your issue.

Thank you so much!!

 

[HPGiroux]

Now another thing that was previously seeming working that now is not: I need the yellow cell to calculate the numbers that populate.