Formula problem

[Bopper29]

Book1
	A	B	C	D	E
1		0.5	1	1.5	Total
2	19	5	14		16.5
3	24	4	19	1	20.5
4	6		6		6
5	4		3	1	4.5
Sheet1

Above is a sample of a spreadsheet I have constructed.

Everything I need is working fine except the Total column. I have tried various formulas but struggled to find the correct solution to give me the answer I require.

Basically the total column is a sum of the first 20 results in column B,C & D using the multiplier in row 1.
Order of preference is column D first followed by C followed by B. Row 3 for example only column D and C are to be used in the total calculation.

Any help would be greatly appreciated

 

[Fluff]

How about
=IF(D2="",C2*C$1+B2*B$1,IF(C2="",D2*D$1+B2*B$1,D2*D$1+C2*C$1))

 

[Bopper29]

Close. But it calculates all values in columns B.C & D should only take the first 20 working in reverse.

EG Row 3 just columns C & D. B should be ignored

 

[Fluff]

I don't understand what you are saying.
The formula I supplied gives the results that you have shown.

 

[dreid1011]

Perhaps this?

=IF(SUM(B2:D2)<=20,D2*D$1+C2*C$1+B2*B$1,IF(SUM(C2:D2)<=20,D2*D$1+C2*C$1,D2*D$1))

Close. But it calculates all values in columns B.C & D should only take the first 20 working in reverse.

EG Row 3 just columns C & D. B should be ignored

A question then, what is D = 15 and C = 7.

Do you calculate only D or do you calculate D and 5 from C?

 

[Bopper29]

I don't understand what you are saying.
The formula I supplied gives the results that you have shown.

I have another part of the sheet which has 0 in B, 3 in C and 23 in D. The correct result should be 30 your formula give 37.5

 

[dreid1011]

I have another part of the sheet which has 0 in B, 3 in C and 23 in D. The correct result should be 30 your formula give 37.5

Then mine will not work either. Based on that, it returns 34.5.

 

[Bopper29]

A question then, what is D = 15 and C = 7.

Do you calculate only D or do you calculate D and 5 from C?

Yes all of D and only 5 from C

 

[dreid1011]

Try this:

=IF(D2>20,20*D$1,IF(C2+D2>20,D2*D$1+(20-D2)*C$1,IF(B2+C2+D2>20,D2*D$1+C2*C$1+(20-C2-D2)*B$1,D2*D$1+C2*C$1+B2*B$1)))

 

[Bopper29]

Try this:

=IF(D2>20,20*D$1,IF(C2+D2>20,D2*D$1+(20-D2)*C$1,IF(B2+C2+D2>20,D2*D$1+C2*C$1+(20-C2-D2)*B$1,D2*D$1+C2*C$1+B2*B$1)))

Works perfect thank you very much