Over limit or Nearly Over Limit

[wayneshirley]

Hi there,

I have a spreadsheet that has a customers credit limit in column E and the customers balance in column F.

I need a formula that will determine which accounts are either over their limit or within 90% of their limit. The results need to show as "yes" if over the limit, "yes" if within 90% of their limit and "no" if under 90% of the limit.

Example 1. Limit $1000, balance $950. This is within 90% of the limit. Results to show as "yes" in column M.

Example 2. Limit $1000, balance $1100. This is over the limit. Results to show as "yes" in column M.

Example 3. Limit $1000, balance $ 500. This is under 90% of the limit. Results to show as "no" in column M.

Thank you.

 

[grizle]

=IF(F2>0.9*E2,"yes","no")

 

[wayneshirley]

Hi Grizle,

Thank you for the formula.

I copied it into my macro but Excel has highlighted it in red. Don't know what the error is. Can you help please?

Thank you.

 

[AMAS]

Maybe try this:

=IF((F2/E2)>0.9,"yes","no")

vba code is:

ActiveCell.FormulaR1C1 = "=IF((RC[-7]/RC[-8])>0.9,""yes"",""no"")"

AMAS

 

[mikerickson]

I note that the Worksheet function in post#4 can return a #DIV/0 error while the one in #2 cannot.

 

[AMAS]

Hi Mike,

I agree, but in this case, I see this as a checkmark to make sure that both cells have some useable data in them. In post #2, the default is always 'no' even if there is missing data.

AMAS

 

[mikerickson]

Change the inequality and either "yes" or "no" becomes the default. An error in the cell will cause errors downstream.