How do I set a number cap for a cell?
 

I am trying to modify an excel program to flow with the changes to a reading
program at our local elementary school. The current program adds the minutes
that each child reads and divides them by the amount of minutes each student
should read at home. If the meet their goal they get a prize if they double
their goal they get two and so on... This year they want to cap it to 5
prizes. The minutes and prizes can carry over, so I am at a loss. The
current function is =ROUNDDOWN((((C11+D11+E11+F11)/300)-(C45+D45+E45)),0)
The 11 rows are the minutes that a student read from Oct.+Nov.+Dec.+Jan. The
45 rows are the number of prizes that they recieved from the corresponding
months (the columns) for their reading minutes. I need to cap it to only
allow 5, but lets say that a child gets 7, I need to carry over 2 to the next
month. Hope that I didn't rattle to much, thanks for any help!

How do I set a number cap for a cell?
 

Hi,

(1) How many gift left available: 5-(C45+D45+E45)
(2) How many more requested now: INT((C11+D11+E11+F11)/300)
Therefore gifts given: if (2)<=(1) then (2) else (1)
so either

=IF(INT((C11+D11+E11+F11)/300)<=5-(C45+D45+E45),INT((C11+D11+E11+F11)/300),5-(C45+D45+E45))
or simpler:
= MIN(5-(C45+D45+E45),INT((C11+D11+E11+F11)/300))

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"rmwarde" wrote:

I am trying to modify an excel program to flow with the changes to a reading
program at our local elementary school. The current program adds the minutes
that each child reads and divides them by the amount of minutes each student
should read at home. If the meet their goal they get a prize if they double
their goal they get two and so on... This year they want to cap it to 5
prizes. The minutes and prizes can carry over, so I am at a loss. The
current function is =ROUNDDOWN((((C11+D11+E11+F11)/300)-(C45+D45+E45)),0)
The 11 rows are the minutes that a student read from Oct.+Nov.+Dec.+Jan. The
45 rows are the number of prizes that they recieved from the corresponding
months (the columns) for their reading minutes. I need to cap it to only
allow 5, but lets say that a child gets 7, I need to carry over 2 to the next
month. Hope that I didn't rattle to much, thanks for any help!

How do I set a number cap for a cell?
 

Thank you very much. It didn't quite work, when I copied and pasted it, but
with a little tweat I got it. Would of never got there with out the help.
This is what I came up with..

=MIN(5,(INT((C10+D10+E10+F10)/300)-(C45+D45+E45)))
There is probably an easier way, but it works..
Thanks a million again
rmwarde