Summing an array based on cell number that can be a decimal

[Sin]

Hi all,

I have had a good look round the forums and still can't find what I am looking for. (Probably because I am not searching for the right thing!!)

I am hoping the below can be dealt with using a new function, but open to all ideas.

I am trying to build a forecasting tool for predicting what I want to buy. However the product I am buying has a very short use by date so I need to buy fractions of a days worth of stock.

Normally I buy 1.5 days worth of stock, but sometimes it's 2.5, sometimes it 0.5. The example in the table below, I want to buy 1.5 days worth of stock. So my order box should say Days Cover (Day 1 + Day 2*0.5)-opening stock = 100. However if I was buying 2.5 days it would be 175Kg and if I was buying 0.5 days stock it would return 0

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Days Cover[/TD]
[TD]Opening stock[/TD]
[TD]Day 1[/TD]
[TD]Day 2[/TD]
[TD]Day 3[/TD]
[TD]Order[/TD]
[/TR]
[TR]
[TD]1.5[/TD]
[TD]100[/TD]
[TD]150[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am hoping somebody may have this already, if not, any ideas.

Thanks

Sin

 

[James006]

Hello,

Could following formula be of any help

=MAX(0,(C2*INT(A2)+(D2*(A2-INT(A2))))-B2)

 

[Special-K99]

Hello,

Could following formula be of any help

=MAX(0,(C2*INT(A2)+(D2*(A2-INT(A2))))-B2)

Doesnt work on the 2.50 Days Cover - returning 250, OP says it should return 175.

 

[Special-K99]

This works on those three input values 0.5, 1.5, 2.5

=IF(A2=0.5,0,IF(A2=1.5,(C2+D2/2)-B2,(C2+D2+E2/2)-B2))

 

[Sin]

Doesnt work on the 2.50 Days Cover - returning 250, OP says it should return 175.

Thanks for the update

I don't want to restrict myself to halves of days and ideally wouldn't want to hardcode in the number of days to check. Ideally it just sums based on the number I put into the days cover box.

 

[Marcelo Branco]

See if this does what you want


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Days Cover​

[/td][td]

Opening stock​

[/td][td]

Day 1​

[/td][td]

Day 2​

[/td][td]

Day 3​

[/td][td]

Order​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1,5​

[/td][td]

100​

[/td][td]

150​

[/td][td]

100​

[/td][td]

50​

[/td][td]

100​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2,5​

[/td][td]

100​

[/td][td]

150​

[/td][td]

100​

[/td][td]

50​

[/td][td]

175​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

0,5​

[/td][td]

100​

[/td][td]

150​

[/td][td]

100​

[/td][td]

50​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

1,6​

[/td][td]

100​

[/td][td]

150​

[/td][td]

100​

[/td][td]

50​

[/td][td]

110​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

3​

[/td][td]

100​

[/td][td]

150​

[/td][td]

100​

[/td][td]

50​

[/td][td]

200​

[/td][/tr]
[/table]

Formula in F2 copied down
=MAX(0,SUMPRODUCT(--(INT(A2)>={1,2,3}),C2:E2)+MOD(A2,1)*INDEX(C2:E2,MIN(3,INT(A2)+1))-B2)

Hope this helps

M.

 

[James006]

@ Marcelo ...

Think you have deciphered the OP 's objective ...

Very Nice Formula .. :wink:

 

[Sin]

@ Marcelo ...

Think you have deciphered the OP 's objective ...

Very Nice Formula .. :wink:

@ Marcelo Cracking result, thanks for the answer!

 

[Marcelo Branco]

@ Marcelo ...

Think you have deciphered the OP 's objective ...

Very Nice Formula .. :wink:

James,

Thank you. I appreciated your comment.

M.

 

[Marcelo Branco]

@ Marcelo Cracking result, thanks for the answer!

You're welcome. I'm glad to help

M.