Max value in formula/function

[SEWINBABY]

Does anyone know of a way that when i do a calculation- can i specify the max
amount of the end value...
Our Fire Department works on a point system, each members gets a point for
each fire call, up to 25. They have to work three work hours to get one
point- the calculation that we want to do is # of work hours/3 with a maximum
of 35

Any Ideas???
THANK YOU

 

[Nick Hodge]

Couple of presumptions

1) The hours are whole numbers. If they are Times in correct Excel format
you will need to multiply the value by 24 to get it to work
2) You only want 'whole' points, not decimals (rounded down)
3) Hours in my example are in B2

=IF(INT((B2/3))>35,35,INT(B2/3))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[RagDyeR]

Assuming that you already have your initial formulas created, try this to
limit the returns:

=MAX(35,"your formula")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Does anyone know of a way that when i do a calculation- can i specify the
max
amount of the end value...
Our Fire Department works on a point system, each members gets a point for
each fire call, up to 25. They have to work three work hours to get one
point- the calculation that we want to do is # of work hours/3 with a
maximum
of 35

Any Ideas???
THANK YOU

 

[SEWINBABY]

Worked Perfect
Thank You Very Much

Couple of presumptions

1) The hours are whole numbers. If they are Times in correct Excel format
you will need to multiply the value by 24 to get it to work
2) You only want 'whole' points, not decimals (rounded down)
3) Hours in my example are in B2

=IF(INT((B2/3))>35,35,INT(B2/3))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)