Solved

# How apply this formula in excel

Posted on 2013-06-07
318 Views
How to convert the formula in excel. These are the conditions

IF marks>=(xbar+sd)) and (marks<(xbar+(1.645*sd))) then grade="A"
IF marks>=(xbar+(0.5*sd))) and (marks<(xbar+sd)) then grade="B+"
IF marks>=(xbar)) and (marks<(xbar+(0.5*sd))) then grade="B"
IF marks>=(xbar-(0.5*sd))) and (marks<xbar) then grade="C+"
IF marks>=(xbar-(1.645*sd)) and (marks<(xbar-sd)) then grade="D"

I have created this formula how to apply this. On cells having numbers only.

=IF(\$E3="","",
IF(\$E3=0,"F",
IF(\$E3=100,"A+",
IF(\$E3>=(\$H\$2+(1.645*\$J\$2)),"A+",
IF(AND((\$E3>=(\$H\$2+\$J\$2)),(\$E3<(\$H\$2+(1.645*\$J\$2)))),"A",
IF(AND((\$E3>=(\$H\$2+(0.5*\$J\$2))),(\$E3<(\$H\$2+\$J\$2))),"B+",
IF(AND((\$E3>=(\$H\$2)),(\$E3<(\$H\$2+(0.5*\$J\$2)))),"B",
IF(AND((\$E3>=(\$H\$2-(0.5*\$J\$2))),(\$E3<\$H\$2)),"C+",
IF(AND((\$E3>=(\$H\$2-\$J\$2)),(\$E3<(\$H\$2-(0.5*\$J\$2)))),"C",
IF(AND((\$E3>=(\$H\$2-(1.645*\$J\$2))),(\$E3<(\$H\$2-\$J\$2))),"D",
IF((\$E3<(\$H\$2-(1.645*\$J\$2))),"F",)))))))))))

The excel sheet is attached.

I want to apply this formula on column f3.
bba-2.xlsx
0
Question by:searchsanjaysharma
• 2

LVL 22

Accepted Solution

rspahitz earned 500 total points
ID: 39229391
You could try surrounding the formula with:

IF(ISNUMBER(...),"")

Also, FYI, you could name your cells so they look closer to your original formula, so column E can be called Marks and H2 can be called xbar, etc
0

Author Comment

ID: 39231118
My question is the formula correct/
0

Author Closing Comment

ID: 39231450
tx
0

## Featured Post

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…