Sum a row of values and redistribute the data in groups of 10.

Gaztron3000

New Member
Joined
Nov 7, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am trying to forecast customer demand for a product. Using order history, i can estimate a customer's average monthly order, however, some values have decimal points and there is a minimum order quantity required.
I want to write a formula that adds up the total value across a row of data (estimated orders per month) and redistributes the data in groups of 10 (minimum order quantity) across the same number of cells.

example: see attached image.

Is it possible to set a different redistribution value for different customers based on known historical order quantities?

Thanks for any help you can give.
 

Attachments

  • Screenshot 2023-11-08 123006.png
    Screenshot 2023-11-08 123006.png
    9.6 KB · Views: 12

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Welcome to the MrExcel board!

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this does what you want

23 11 08.xlsm
ABCDEFG
1
2A44545
3B1616201620
4C16.816.82116.821
5
6
7A  10 102
8B10202010208
9C10202020202.4
Demand
Cell Formulas
RangeFormula
B7:F9B7=LET(f,SUM($B2:B2),u,SUM($A7:A7),IF(f>u+10,INT((f-u)/10)*10,""))
G7:G9G7=SUM(B2:F2)-SUM(B7:F7)
 
Upvote 1
Solution
Thank you, Peter. You are a genius.
This is my first time using the site, so my apologies for not using the XL2BB. I will be sure to use it next time.
Thanks for your help.
 
Upvote 0
You're welcome, but I believe that there is an error with my formula (Missing an = sign)

See the example below. My original formula is in row 7 and what I think the corrected version should be in row 12.

23 11 08.xlsm
ABCDEFG
2A10641010
6
7A 10 20 10
11
12A10 1010100
Demand
Cell Formulas
RangeFormula
B7:F7B7=LET(f,SUM($B2:B2),u,SUM($A7:A7),IF(f>u+10,INT((f-u)/10)*10,""))
G7G7=SUM(B2:F2)-SUM(B7:F7)
B12:F12B12=LET(f,SUM($B2:B2),u,SUM($A12:A12),IF(f>=u+10,INT((f-u)/10)*10,""))
G12G12=SUM(B2:F2)-SUM(B12:F12)
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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
Back
Top