Quote:
Originally Posted by JPCleary
Hi. I have Excel 2007. I'm trying to create a spreadsheet for my teenage daughter to illustrate our monetary incentive plan for grades. Ordinarily I can pretty well figure out anything I want to do in Excel with the help of Google, but in this case, I don't know what to even search.
The plan works like this. Each A is worth $5.00. No other grade is worth money. There are 5 classes. As a kicker, if she receives A's in all 5 classes, the worth of those A's doubles, making them worth $10.00 each. So if she received 4 A's and 1 B...that would be $20.00. If she received 5 A's...that would be worth $50.00.
I want to create a spreadsheet that would calculate this. A grade of 93 and above is an A...92 and below calculates to $0.00. So as an example...if cell A2 were the title of the class, B2 were the numeral grade of 95, C2 would be the formula and would display $5.00. To make it a bit more complicated, the formula has to account for the grades put into each of the other classes, cells B3, B4, B5 and B6, because if each one of those cells contains a 93 or higher, the value of each of the C cells doubles to $10.00.
Thanks so much for the help. My daughter and I both appreciate it.
|
It can be done with a "helper column" quite easily.
In your scenario you have class titles in A2 to A6, the grades obtained will go into B2 to B6.
Enter
=IF(B2=93,1,"") in D2 and copy it down to D6 then hide the column.
Then enter
=IF(AND(B2=93,SUM($D$2:$D$6)=5),10,IF(B2=93,5,"" )) in C2 and copy down to C6.
As you put the grades in you'll see how it all works.
I'm sure there are neater ways of doing this, but this should do you for now. :)