Combined Min & Max Nested Excel Formula

[johnnie1]

I am wanting to calculate the maximum dividend payout in the excel worksheet below for my business.I “cannot pay out more than the cash available OR than what I have available as distributable reserves”.

Apparently, there is a nested maxif and minif function/formula I heard that can be used for this within excel? Is anyone aware of this? Let me know please as am I not savvy with excel.

Your advice would be most appreciated. I just need 1 formula in the purple shaded cell references within the excel s/sheet. I am using MS Office 365 Excel for Mac. See my workings below. Thanks

Mr Excel.com Query.xlsx
	A	B	C	D	E	F	G	H
1		Y1	Y2	Y3	Y4	Y5	Y6	Y7
2
3	Closing cash	100	200	(100)	(300)	500	1,000	100
4	Distributable reserves	150	150	50	(50)	300	800	200
5
6	Maximum dividend
7
8	Min Forumla	100	150	(100)	(300)	300	800	100
9	Max	150	200	50	(50)	500	1,000	200
10	Payout (manual calc)	100	150			300	800	100
11
12	Works				Worst		Best
13					Worst		Best
14		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
15		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
16		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
17		#NAME?
18		FALSE
19
20	Values	0.00	0.00	0.00	0.00	0.00	0.00	0.00
21		-50.00	-50.00	-50.00	-50.00	0.00	0.00	0.00
Sheet1

Cell Formulas
Range	Formula
B8:H8	B8	=MIN(B3:B4)
B9:H9	B9	=MAX(B3:B4)
B12:H13	C12	=IF(C$3=MAX($B$3:$H$3),"Best",IF(C$4=MIN($B$4:$H$4),"Worst",""))
B14:H15,B18	C14	=IF(C$3=MAX($B$3:$H$3),IF(C$4=MIN($B$4:$H$4),""))
B16:H16	C16	=IF(C$3=MAX($B$3:$H$3),IF(C$4=MIN($B$4:$H$4),))
B17	B17	=MAX=B$3,($B$3:$H$3),MIN=B$4($B$4:$H$4)
B20:H20	B20	=MAX(IF(B3:H3=B3,MIN(IF(B3:H3=B4,B4:H4),)))
B21:H21	B21	=MAX(IF(B3:H3,MIN(IF(B4:H4,B4:H4),)))

 

[alz]

Is that you want?

Excel Message board.xlsm
	A	B	C	D	E	F	G	H
1		Y1	Y2	Y3	Y4	Y5	Y6	Y7
2
3	Closing cash	100	200	-100	-300	500	1000	100
4	Distributable reserves	150	150	50	-50	300	800	200
5
6	Maximum dividend	100	150			300	800	100
Sheet4

Cell Formulas
Range	Formula
B6:H6	B6	=IF(B3<0,"",IF(B4>B3,B3,B4))

 

[johnnie1]

Is that you want?

Excel Message board.xlsm
	A	B	C	D	E	F	G	H
1		Y1	Y2	Y3	Y4	Y5	Y6	Y7
2
3	Closing cash	100	200	-100	-300	500	1000	100
4	Distributable reserves	150	150	50	-50	300	800	200
5
6	Maximum dividend	100	150			300	800	100
Sheet4

Cell Formulas
Range	Formula
B6:H6	B6	=IF(B3<0,"",IF(B4>B3,B3,B4))

First of all ALZ user thanks so much for your assistance.

Yes this formula does work you provided in giving the correct answer but via an IF statement. I need use min and max functions within that IF statement you provided to be used for forthcoming requirements in the future. Is this something your knowledge extends ? I am not an advanced User of excel.

Thanks once again.

 

[alz]

I can only combine with min function since your argument is defined by lower bound .
And Some one with office 365 can assist for Minif function.

Excel Message board.xlsm
	A	B	C	D	E	F	G	H
1		Y1	Y2	Y3	Y4	Y5	Y6	Y7
2
3	Closing cash	100	200	-100	-300	500	1000	100
4	Distributable reserves	150	150	50	-50	300	800	200
5
6	Maximum dividend	100	150			300	800	100
7		100	150			300	800	100
Sheet4

Cell Formulas
Range	Formula
B6:H6	B6	=IF(B3<0,"",IF(B4>B3,B3,B4))
B7:H7	B7	=IF(B3<0,"",MIN(B3:B4))

 

[Lian]

工作簿1
	A	B	C	D	E	F	G	H
1		Y1	Y2	Y3	Y4	Y5	Y6	Y7
2
3	Closing cash	100	200	-100	-300	500	1000	100
4	Distributable reserves	150	150	50	-50	300	800	200
5
6	Maximum dividend	100	150			300	800	100
Sheet2

Cell Formulas
Range	Formula
B6:H6	B6	=IF(B3<0,"",MIN(B3:B4))

 

[Lian]

Minifs might not identify some cases, the critieria is filiting the true results.

工作簿1
	A	B	C	D	E	F	G	H
1		Y1	Y2	Y3	Y4	Y5	Y6	Y7
2
3	Closing cash	100	200	-100	-300	500	1000	100
4	Distributable reserves	150	150	50	-50	300	800	200
5
6	Maximum dividend	100	150	50	0	300	800	100
Sheet1

Cell Formulas
Range	Formula
B6:H6	B6	=MINIFS(B3:B4,B3:B4,">0")

 

[johnnie1]

Minifs might not identify some cases, the critieria is filiting the true results.

工作簿1
	A	B	C	D	E	F	G	H
1		Y1	Y2	Y3	Y4	Y5	Y6	Y7
2
3	Closing cash	100	200	-100	-300	500	1000	100
4	Distributable reserves	150	150	50	-50	300	800	200
5
6	Maximum dividend	100	150	50	0	300	800	100
Sheet1

Cell Formulas
Range	Formula
B6:H6	B6	=MINIFS(B3:B4,B3:B4,">0")

Minifs might not identify some cases, the critieria is filiting the true results.

工作簿1
	A	B	C	D	E	F	G	H
1		Y1	Y2	Y3	Y4	Y5	Y6	Y7
2
3	Closing cash	100	200	-100	-300	500	1000	100
4	Distributable reserves	150	150	50	-50	300	800	200
5
6	Maximum dividend	100	150	50	0	300	800	100
Sheet1

Cell Formulas
Range	Formula
B6:H6	B6	=MINIFS(B3:B4,B3:B4,">0")

Hi Lian - thanks for your help. Your formula works but not for Y3 and Y4 years in the table as these are negative balances & I should not be paying dividends to customers with a negative bank/reserve bal if you see what I mean? For Y3 & Y4 the value should be nil or 0.

What needs to be tweaked in this formula still =MINIFS(B3:B4,B3:B4,">0") still using min or max or minIF(S) or MAXIF(S)

Thanks once again for everyone's support. Cheers