Difficult formula! Need help quick!

[jimmyz]

This is the spreadsheet I am trying to fill out.
Where Cost= cost of class, Budget Impact= total-cost, Complete= Course
completion ("Y","N"), Grade= Letter grade OR Percentage ("A"=93 to 100,
"D"=70 to 76), Budget= Budget - Percent of cost

F G H
I J
1 Cost Budget Impact Complete Grade Budget
2 $6,000.00 $6,000.00
3 $196.00 $5,804.00 Y 100 FALSE
4 $77.00 $5,727.00
5 $77.00 $5,650.00
6 $98.00 $5,552.00
7 $60.00 $5,492.00
8 $60.00 $5,432.00
9 $60.00 $5,372.00
10 $60.00 $5,312.00
11 $77.00 $5,235.00
12 $78.00 $5,157.00
13 $98.00 $5,059.00
14 $196.74 $4,862.26

M N O P PERCENT OF COST
1 A B C D A= 100%, B= 75%, C= 50%, D= 0%
2 100 92 84 76
3 99 91 83 75
4 98 90 82 74
5 97 89 81 73
6 96 88 80 72
7 95 87 79 71
8 94 86 78 70
9 93 85 77

Name Define = "a"
=IF(AND(H3="Y",OR(I3=M1,I3=M2,I3=M3,I3=M4,I3=M5,I3=M6,I3=M7,I3=M8,I3=M9)),G3,"")

Name define = "b"
=IF(AND(H3="Y",OR(I3=N1,I3=N2,I3=N3,I3=N4,I3=N5,I3=N6,I3=N7,I3=N8,I3=N9)),J2-F3*0.75,"")

Name Define = "e"
=IF(AND(H3="Y",OR(I3=O1,I3=O2,I3=O3,I3=O4,I3=O5,I3=O6,I3=O7,I3=O8,I3=O9)),J2-F3*0.5,"")

Name define = "f"
=IF(AND(H3="Y",OR(I3=P1,I3=P2,I3=P3,I3=P4,I3=P5,I3=P6,I3=P7,I3=P8,I3=P9)),J2,"")

I was hoping to auto calculate the Budget column but I ran into a glitch
with the formula below:

=IF(H3="Y",OR(I3=a,I3=b,I3=e,I3=f),"") 'Returns "False" or "True" rather
than reduction in Budget column

How can I adjust this logic function? Function Names: a,b,e,f. All work
independently of each other but I need to enclose them all in one function.

 

[Rod]

The OR function only returns True or False. The function will always return
True or False if the first argument is True and blank if false. Since your %
are named ranges, you could use a vlookup with the named range in the
calculation:
=IF(H3="Y",vlookup(to retrieve amount),"")
confusing how you would return a value with this formula other than a %.
But you could further edit the function in the 'if true' segment to multiply
that % or what have you.

 

[Bob Phillips]

=IF(H3<>"Y","",J2-F3*LOOKUP(I3,{0,77,85,93;0,0.5,0.75,1}))

no need for the other table M-P

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jimmyz]

Bob
I also have a no in the formula under Completed. When I put a N in this cell
a #value comes up in the Budget cell. would there be an "and" in this
formula?

 

[Bob Phillips]

It comes up blank for me Jimmy, =IF(H3<>"Y","", ...

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)