Combined Min & Max Nested Excel Formula

johnnie1

New Member
Joined
Jan 30, 2021
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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

1611997170123.png



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),)))
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jan 30, 2021
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jan 26, 2021
Messages
33
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

工作簿1
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
Joined
Jan 26, 2021
Messages
33
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Minifs might not identify some cases, the critieria is filiting the true results.
工作簿1
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
Joined
Jan 30, 2021
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
Minifs might not identify some cases, the critieria is filiting the true results.
工作簿1
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.
工作簿1
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,294
Messages
5,623,829
Members
415,993
Latest member
Nawlins

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top