Max formula for multiple columns

[david col]

Hi
I have a sheet where I am looking for a formula to add to C14,D14,E14,F14.G14 & H14 to calculate 15% of C13,D13,E13,F13,G13 & H13 up to a limit e.g. the maximum is 20,000 over a few months month. E.G if profit is sufficient in cells C14,D14 & E14 or any combination of columns where 15|% of the total exceeds the 20,000, I need the last column to just result in the balance e.g. if only say 5,000 owing at column E is then E14 would result just 5,000 and the remaining cells F14,G14 & H14 would need to result in zero. Similarly if C14 happened to result in the full 20,000, all other cells D14, E14 etc would result in Zero.

Image attached.

Any help with this is appreciated thanks.

I am using Excel 2010.

 

[jtakw]

Hi,

You probably should have shown Expected Results in C14:H14 to help clarify your description...
Is this what your want, formula requires B14 to be Blank.

Book3.xlsx
	A	B	C	D	E	F	G	H
1
2
3		Loan	15000		Payback	15%	of Profit Monthly
4
5
6
7
8
9
10
11
12
13			20000	25000	21000	37000	27000	42000
14			3000	3750	3150	5100	0	0
Sheet922

Cell Formulas
Range	Formula
C14:H14	C14	=IF(SUM($B14:B14,C13*$F3)>=$C3,$C3-SUM($B14:B14),C13*$F3)

 

[david col]

Hi,

You probably should have shown Expected Results in C14:H14 to help clarify your description...
Is this what your want, formula requires B14 to be Blank.

Book3.xlsx
	A	B	C	D	E	F	G	H
1
2
3		Loan	15000		Payback	15%	of Profit Monthly
4
5
6
7
8
9
10
11
12
13			20000	25000	21000	37000	27000	42000
14			3000	3750	3150	5100	0	0
Sheet922

Cell Formulas
Range	Formula
C14:H14	C14	=IF(SUM($B14:B14,C13*$F3)>=$C3,$C3-SUM($B14:B14),C13*$F3)

Fantastic that is spot on .. Many thanks I do appreciate the help

 

[jtakw]

You're welcome, thanks for the feedback.