Hi,

I am trying to create a cell (T5) that refers to another cell (S5) that contains a value / number, but I don't want the T5 value to exceed 60, at the same time not fall below 0.

I know the functions of =MIN(60, and =MAX(0, . But is it possible to have both work together to achieve what I want?

Thanks!

Hi Kristy,

Am Wed, 11 Feb 2015 06:28:32 +0000 schrieb KristyAlison:

I am trying to create a cell (T5) that refers to another cell (S5) that
contains a value / number, but I don't want the T5 value to exceed 60,
at the same time not fall below 0.

I know the functions of =MIN(60, and =MAX(0, . But is it possible to
have both work together to achieve what I want?

your problem is not really clear. Can you post some examples of numbers
in S5 and the expected output in T5?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hello Claus, thank you for coming to my aid.

Basically, I am working on a library-based Excel file.

What happens is, the Excel has a check out date (Cell A), and a check in date (Cell B). If books are returned exceeding 30 days (Cell B - Cell A), this would be reflected in Cell C on showing the number of days it's been overdue. A fine would also be calculated in Cell D, $1 is charged for every day overdue, with a maximum of $60 that we are allowed to collect by policy.

The problem is here, at times when books are still not returned over 60 days, (say 61 days), I want the cell to show a maximum of 60 only coz that's the maximum we can collect), at the same time, I don't want cells to show "-42076" when books are not returned yet.

Is there a solution to this?

Hi Kristy,

Am Wed, 11 Feb 2015 07:56:28 +0000 schrieb KristyAlison:

What happens is, the Excel has a check out date (Cell A), and a check in
date (Cell B). If books are returned exceeding 30 days (Cell B - Cell
A), this would be reflected in Cell C on showing the number of days it's
been overdue. A fine would also be calculated in Cell D, $1 is charged
for every day overdue, with a maximum of $60 that we are allowed to
collect by policy.

in C1 try:
=IF(B1-A130,B1-A1-30,0)
and in D1 try:
=MIN(C1,60)
Format columns C and D with custom numberformat:
0;-0;
The last semicolon is important to avoid dispaying 0


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"KristyAlison"
I am trying to create a cell (T5) that refers to another cell (S5) that
contains a value / number, but I don't want the T5 value to exceed 60,
at the same time not fall below 0. I know the functions of =MIN(60, and
=MAX(0, . But is it possible to have both work together to achieve
what I want?

Perhaps:

=MIN(60,MAX(0,S5))


"KristyAlison"
What happens is, the Excel has a check out date (Cell A), and a check in
date (Cell B). If books are returned exceeding 30 days (Cell B - Cell
A), this would be reflected in Cell C on showing the number of days it's
been overdue. A fine would also be calculated in Cell D, $1 is charged
for every day overdue, with a maximum of $60 that we are allowed to
collect by policy.

The problem is here, at times when books are still not returned over 60
days, (say 61 days), I want the cell to show a maximum of 60

If A1 is the check-out date, B1 is the check-in date and C1 is the daily
charge:

=C1*MIN(60,MAX(0,B1-A1-30))

If B1-A1 does not exceed 30, MAX returns 0, and MIN returns 0. So =C1*0
returns 0.

If B1-A1 is more than 30 but does not exceed 60, MAX returns B1-A1, and MIN
returns B1-A1. So =C1*(B1-A1) is $1 per day if C1=1.

If B1-A1 is more than 60, MAX returns B1-A1, but MIN returns 60. S0 =C1*60
returns $60 if C1=1, the maximum charge.

Errata....
"joeu2004" wrote:
"KristyAlison"
What happens is, the Excel has a check out date (Cell A), and a check in
date (Cell B). If books are returned exceeding 30 days (Cell B - Cell
A), this would be reflected in Cell C on showing the number of days it's
been overdue. A fine would also be calculated in Cell D, $1 is charged
for every day overdue, with a maximum of $60 that we are allowed to
collect by policy.

The problem is here, at times when books are still not returned over 60
days, (say 61 days), I want the cell to show a maximum of 60

If A1 is the check-out date, B1 is the check-in date and C1 is the daily
charge:

=C1*MIN(60,MAX(0,B1-A1-30))

I just read your posting more closely. I see that you used C1 ("cell C")
for another purpose.

Perhaps __your__ C1 is:

=B1-A1-30

and __your__ D1 is:

=E1*MIN(60,MAX(0,C1))

where E1 is the daily charge. E1 is 1 in your example.

Wow great! Thank you all!