asked on

issue with excel formula

Hello all

I have an issue with a formula in one cell:

=+SI(ESTVIDE(D9),"-",SI(D9="-","-",SI(D9>=2%,"B",SI(D9>=0,"G",SI(D9>=-1%,"A",SI(D9>=-2%,"R"))))))

Open in new window

This formula is in cell column E.

So if for example, in cell B of the same row, i have the value: 1546

And in column C: 1472
then, in column D, i have a formula : =(C12-B12)/B12
That will give the result: -5%

So with the big formula where i have an issue, i should get the result "G"
But i have FALSE.

But if i change the formula =(C12-B12)/B12 with the real result -5%, then, it give me the result G.

Can you help me with that?

Thanks

test.xlsx

Neil Russell

Because cell D12 contains a formula that gives a numeric answer. All you have done is changed the display format so it SHOWS -5%

Your compares need to be again actual values.

=IF(ISBLANK(D12),"-",IF(D12="-","-",IF(D12>=0,"B",IF(D12>=0,"G",IF(D12>=-0.01,"A",IF(D12>=-0.02,"R","Out of Range"))))))

ASKER CERTIFIED SOLUTION

regmigrant

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

Thanks to both of you, since R is my last result i can get, i have changed it to SI(D12>=-100%,"R") instead of SI(D12>=-2%,"R").

Thanks again

regmigrant

just a style point on your final comment - if D12 is ever lower than -100% you will get False again. If you are sure this can't happen its ok but from an elegance point of view you dont need the final 'Si', if you always want to return "R" at this point you can just put "R" because there is no other result