Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difficult Formula | Excel Worksheet Functions | |||
Difficult Formula | Excel Worksheet Functions | |||
Difficult (at least to me) formula question | Excel Worksheet Functions | |||
Difficult look up formula | Excel Worksheet Functions | |||
Difficult (for me) formula/UDF calculation | Excel Worksheet Functions |