# Combined Min & Max Nested Excel Formula

#### johnnie1

##### New Member
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
ABCDEFGH
1Y1Y2Y3Y4Y5Y6Y7
2
3Closing cash100200(100)(300)5001,000100
4Distributable reserves15015050(50)300800200
5
6Maximum dividend
7
8Min Forumla100150(100)(300)300800100
9Max15020050(50)5001,000200
10Payout (manual calc)100150300800100
11
12Works   Worst Best
13   Worst Best
14FALSEFALSEFALSEFALSEFALSEFALSEFALSE
15FALSEFALSEFALSEFALSEFALSEFALSEFALSE
16FALSEFALSEFALSEFALSEFALSEFALSEFALSE
17#NAME?
18FALSE
19
20Values0.000.000.000.000.000.000.00
21-50.00-50.00-50.00-50.000.000.000.00
Sheet1
Cell Formulas
RangeFormula
B8:H8B8=MIN(B3:B4)
B9:H9B9=MAX(B3:B4)
B12:H13C12=IF(C\$3=MAX(\$B\$3:\$H\$3),"Best",IF(C\$4=MIN(\$B\$4:\$H\$4),"Worst",""))
B14:H15,B18C14=IF(C\$3=MAX(\$B\$3:\$H\$3),IF(C\$4=MIN(\$B\$4:\$H\$4),""))
B16:H16C16=IF(C\$3=MAX(\$B\$3:\$H\$3),IF(C\$4=MIN(\$B\$4:\$H\$4),))
B17B17=MAX=B\$3,(\$B\$3:\$H\$3),MIN=B\$4(\$B\$4:\$H\$4)
B20:H20B20=MAX(IF(B3:H3=B3,MIN(IF(B3:H3=B4,B4:H4),)))
B21:H21B21=MAX(IF(B3:H3,MIN(IF(B4:H4,B4:H4),)))

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### alz

##### Board Regular
Is that you want?

Excel Message board.xlsm
ABCDEFGH
1Y1Y2Y3Y4Y5Y6Y7
2
3Closing cash100200-100-3005001000100
4Distributable reserves15015050-50300800200
5
6Maximum dividend100150  300800100
Sheet4
Cell Formulas
RangeFormula
B6:H6B6=IF(B3<0,"",IF(B4>B3,B3,B4))

#### johnnie1

##### New Member
Is that you want?

Excel Message board.xlsm
ABCDEFGH
1Y1Y2Y3Y4Y5Y6Y7
2
3Closing cash100200-100-3005001000100
4Distributable reserves15015050-50300800200
5
6Maximum dividend100150  300800100
Sheet4
Cell Formulas
RangeFormula
B6:H6B6=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

##### Board Regular
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
ABCDEFGH
1Y1Y2Y3Y4Y5Y6Y7
2
3Closing cash100200-100-3005001000100
4Distributable reserves15015050-50300800200
5
6Maximum dividend100150  300800100
7100150  300800100
Sheet4
Cell Formulas
RangeFormula
B6:H6B6=IF(B3<0,"",IF(B4>B3,B3,B4))
B7:H7B7=IF(B3<0,"",MIN(B3:B4))

#### Lian

##### New Member

ABCDEFGH
1Y1Y2Y3Y4Y5Y6Y7
2
3Closing cash100200-100-3005001000100
4Distributable reserves15015050-50300800200
5
6Maximum dividend100150  300800100
Sheet2
Cell Formulas
RangeFormula
B6:H6B6=IF(B3<0,"",MIN(B3:B4))

#### Lian

##### New Member
`Minifs` might not identify some cases, the critieria is filiting the true results.

ABCDEFGH
1Y1Y2Y3Y4Y5Y6Y7
2
3Closing cash100200-100-3005001000100
4Distributable reserves15015050-50300800200
5
6Maximum dividend100150500300800100
Sheet1
Cell Formulas
RangeFormula
B6:H6B6=MINIFS(B3:B4,B3:B4,">0")

#### johnnie1

##### New Member
`Minifs` might not identify some cases, the critieria is filiting the true results.

ABCDEFGH
1Y1Y2Y3Y4Y5Y6Y7
2
3Closing cash100200-100-3005001000100
4Distributable reserves15015050-50300800200
5
6Maximum dividend100150500300800100
Sheet1
Cell Formulas
RangeFormula
B6:H6B6=MINIFS(B3:B4,B3:B4,">0")
`Minifs` might not identify some cases, the critieria is filiting the true results.

ABCDEFGH
1Y1Y2Y3Y4Y5Y6Y7
2
3Closing cash100200-100-3005001000100
4Distributable reserves15015050-50300800200
5
6Maximum dividend100150500300800100
Sheet1
Cell Formulas
RangeFormula
B6:H6B6=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

Replies
12
Views
149
Replies
8
Views
118
Replies
3
Views
151
Replies
2
Views
121
Replies
3
Views
102

1,127,521
Messages
5,625,295
Members
416,087
Latest member
drth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back