Excel Formula help?

Question

I am building a class database tracking good behavior points.
Every nine weeks if a student has earned 15 or more points they are invited to a sundae party. The students who are invited to the party have 15 points deducted from their total at the start of the next nine week cycle, those who do not get invited keep their total points.
What formula can I use if excel to deduct the 15 points from those who have that many and then keep the difference (for example if the student has earned 18 points, 18-15=3, they keep those 3 points going into the next 9 week period). At the same time I want excel to keep those students who may of only earned 12 points in the first nine weeks to track those points into the second nine week period.

Thanks

Answer 1

We'll assume that eash student has a row and that there is a column for each nine weeks. You would add a column between each of these for "Balance forward".
As an example,
Column A - Student's name
Column B - Student's FIRST 9 weeks points earned.
Column C- Balance forward

This column (C) in each cell would have the following formula.
=IF(a1>15, A1-15, a1)
This states that if the amount in Column A is greater than 15, to subtract 15 from the total and put that amount in the cell. If the amount is less than 15, then the amount from Column A is placed inot the cell in Column B (carried forward)

continuing:
Column D - Second Nine Weeks Points earned
Column E - Balance forward
This column would have the following formula:
=if(C1+D1>15,(C1+D1)-15,C1+D1)

In this cell, the formula adds the resulting balance of the first nine weeks to the 2nd Nine weeks and then figures as above, whether the total is above 15 or not. Above 15 then 15 is subtracted, and if below 15 the balance is carried forward.

The last columns for the 3rd Nine weeks and 4th nine weeks would all use the 2nd formua referencing those columns.

Column F - 3rd Nine Weeks Points Earned
Column G - Balance forward
Formula: =if(E1+F1>15,(E1+F1)-15,E1+F1)
Column H - 4th Nine Weeks Points Earned
Column I - Balance Forward
Formula: =if(G1+H1>15,(G1+H1)-15,G1+H1)

Answer 2

IF their current score is in cell A1, Make their new score:

=MOD(A1,15)

A score less than 15 will remain unchanged. A score of 15 will change to 0, 16 changes to 1, 17 to 2, etc.
The only problem with this function is that if a student gets to 30 points or higher, they lose 30 points. If they somehow got to 45 points or higher, they'd lose 45 points, and so on for each multiple of 15.

Answer 3

Tricky question. It all depends on how you add in the points in the first place. I would use a system to use Y/N or 1/0 for points with serial calculations for total. Use an If/then statement to say "If (total point) is equal to or greater than 15 then "Party") then you can make another statement "if "party" then -15). You'll have to play with it a bit to get it.

Answer 4

You are going to need multiple rows or columns (depending on how you build it). The formulas are the way you already established but in a second row or column. I will suggest you start practicing with an access database. Excel becomes obsolete when you have to enter the same data more than once. The purpose of a database is to enter data only once.

Answer 5

Use the IF operator. The operator is written as:

IF(logical test ,value if true, value if false)

In your example you'd have something like:

=IF(A10>=15, A10-15, A10)

This is assuming that the value you are operating on is in Cell A10. The first part of the equation tests to see if A10 is greater than or equal to 15. The second part deducts 15 from the value in A10 if the test in the first part proves true. The third part returns the value that was originally in A10 if the test statement proves false.

Answer 6

I will try to get back to if you leave this question open for two day I will be able to give you the mathematical equations for each cell...

I Got It.


Here is an example

Look at cell U2

=+IF(K2>15,K2-15+(+SUM(L2:T2)),K2+(+SUM(L2:T2)))

This checks the previous subtotal and if it is over 15 it subtracts 15
for the new subtotal.

Answer 7

get a measuring tape and some more workers