Difficult formula! Need help quick!
 

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.

Difficult formula! Need help quick!
 

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.


"jimmyz" wrote:

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.

Difficult formula! Need help quick!
 

=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" wrote in message
...
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.

Difficult formula! Need help quick!
 

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" wrote:

=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" wrote in message
...
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.

Difficult formula! Need help quick!
 

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)

"jimmyz" wrote in message
...
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" wrote:

=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" wrote in message
...
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.