MIN/MAX in same cell

[lite4d]

I have a spreadsheet that counts records between dates. As you change the dates, the numbers will change. I have to have a min number of records for 7 days but if not then post what you have during those dates..

C4 can be "Daily" or Weekly" but we just pull weekly.

C5=Total records within the dates
C6=max of 15 but if C5 is lower than C6 then C5

What I have that isn't working:

=ROUNDUP(IF(C4="Weekly",MAX(MIN(C5,15/7),0)*NETWORKDAYS(C3,D3,Calendar!E3:E17)),0)

 

[NateSC]

Where is this equation going on the sheet? C6? Somewhere else?

The MIN calculation is comparing C5 against 15/7 (2.143). Will C5 ever be lower than 2.143?

 

[lite4d]

The formulas is going in C6. Yes C5 could be as low as 1. If it's zero it shows blank

9/30/2024	10/3/2024
Weekly
11
12

 

[NateSC]

I get a different result with those inputs (I did take out the holiday from the date calc, but it shouldn't be needed for those dates):

Book1
	C	D
3	9/30/2024	10/3/2024
4	Weekly
5	11
6	9
Sheet1

Cell Formulas
Range	Formula
C6	C6	=ROUNDUP(IF(C4="Weekly",MAX(MIN(C5,15/7),0)*NETWORKDAYS(C3,D3)),0)

 

[NateSC]

Just to walk through the equation logic assuming C4="Weekly" (otherwise you get a 0):
1) determine the smaller value between C5 or 15/7 - For this case, 15/7 < 11 so 2.143 is the result
2) determine the larger value between the previous result (2.143) and 0. So 2.143 is the result.
3) calculate the workdays - the result is 4
4) multiply result 2 x result 3 - 2.143*4 = 8.571
5) round up -> 9

I didn't see a limit for C6 that would make the max 15 or anything that would make it equal to C5 if it is less than C5.

Talk me through the logic in a little more detail and we can probably get there.

 

[lite4d]

C5 is the total records found between the dates listed in C3 and D3. If C5 is greater than 15 then C6 will be 15. If C5 is less than C6 then C5. This is just saying that if you have more than 15 you only require 15. If you have less than 15 then you only require the amount you have,.

 

[NateSC]

Then would C6 be:
=IF(C5>15,15,C5)

 

[lite4d]

That worked. I guess I was over thinking it. Thanks

 

[NateSC]

No problem. I've done that many times.

 

[Joe4]

Then would C6 be:
=IF(C5>15,15,C5)

That can be simplified to:

=MIN(C5,15)