• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

FORMULA TO DISTRIBUTING VALUES AUTOMATICALLY ACROSS CELLS|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / FORMULA TO DISTRIBUTING VALUES AUTOMATICALLY ACROSS CELLS|General Excel Questions & Answers|Excel Forum|My Online Training Hub

sale now on

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…FORMULA TO DISTRIBUTING VALUES AUTO…
sp_PrintTopic sp_TopicIcon
FORMULA TO DISTRIBUTING VALUES AUTOMATICALLY ACROSS CELLS
Avatar
Meyanui
Member
Members
Level 0
Forum Posts: 22
Member Since:
November 9, 2016
sp_UserOfflineSmall Offline
1
November 10, 2016 - 7:18 pm
sp_Permalink sp_Print

I NEED A SITUATION WHERE IF ANY AMOUNT IS PAID, IT AUTOMATICALLY DISTRIBUTES THE PAID AMOUNT ACROSS THE GIVEN CELLS WITH THE CORRESPONDING AMOUNTS.

NOTE THAT ONE CELL MUST BE COMPLETE BEFORE OVERFLOWING TO THE NEXT.

I HOPE CATALIN NOW UNDERSTANDS WHAT I HAVE BEEN TRYING TO EXPLAIN

 THANK YOU

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
November 10, 2016 - 8:20 pm
sp_Permalink sp_Print

Hi Meyanui,

Try this formula in F5:

=IF(AND(F$2>0,$D5-SUM($E5:E5)>0),MIN(F$2,$D5-SUM($E5:E5)),0)

You can copy it to the right and down as needed.

Sample file also attached.

Avatar
Meyanui
Member
Members
Level 0
Forum Posts: 22
Member Since:
November 9, 2016
sp_UserOfflineSmall Offline
3
November 11, 2016 - 7:49 pm
sp_Permalink sp_Print sp_EditHistory

Wow, Catalin, you are great. I am almost arriving there. Thank you so much

Avatar
Meyanui
Member
Members
Level 0
Forum Posts: 22
Member Since:
November 9, 2016
sp_UserOfflineSmall Offline
4
November 11, 2016 - 8:13 pm
sp_Permalink sp_Print sp_EditHistory

If I may ask, what is the essence of reading the empty cell (E5:E5).

Suppose that column (E) is deleted to make my work appear neat what can I do? 

I asked because when I did it affected the other cells across.

An explanation of this formula would be of great help. 

Thank you once more

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
5
November 12, 2016 - 5:22 am
sp_Permalink sp_Print

You better hide that column, because the formula needs a starting point, to calculate the cumulated values. And that starting point cannot be in column D, and not in column F, where we have the first values.

SUM($E5:E5) , when you copy the formula to the right, will expand: in the last column will be SUM($E5:P5) (the $ sign locks the column reference for the first range, only the second range will increase)

Basically, the formula can be translated in:

"If there is a value in row 2, current column ($F2>0), AND the PAID amount is higher than the cumulated values , ($D5-SUM($E5:E5)>0), Return the minimum value between the row 2 value and the cumulated values (MIN(F$2,$D5-SUM($E5:E5))). "

Avatar
Meyanui
Member
Members
Level 0
Forum Posts: 22
Member Since:
November 9, 2016
sp_UserOfflineSmall Offline
6
November 12, 2016 - 6:52 pm
sp_Permalink sp_Print sp_EditHistory

Catalin, you are great. You have been of great assistance.Thank you so much

Avatar
Owais Ashraf

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
October 27, 2020
sp_UserOfflineSmall Offline
7
October 27, 2020 - 6:27 pm
sp_Permalink sp_Print sp_EditHistory

@catalin...you rock buddy even after 5 years your formulae gives a perfect solution

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
October 28, 2020 - 2:03 am
sp_Permalink sp_Print

Thank you

I guess a good formula never dies 🙂

Avatar
Owais Ashraf

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
October 27, 2020
sp_UserOfflineSmall Offline
9
October 28, 2020 - 5:40 pm
sp_Permalink sp_Print sp_EditHistory

Dear Catalin,

need some assistance with attached file

Actually i used your formulae and everything works great but what i am stuck at is if i have a issue with a particular location i need to distribute its Quantity in available regions based on distance from Location 1 , independently for all rows 

example i have 5 locations and location 1 has issue so i cannot store the goods there , so i need formulae to distribute goods of location 1 into other 4 based on my priority, say first priority location 2 then 5 and then 4 and last 3

 how can ammend yiur formulae and  ensure quantity are distributed based on % say  60 % should go to first cell then 20% next cell and remain in 3rd cell 

 

Will highly appreciate you thoughts and solution.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
October 29, 2020 - 7:03 am
sp_Permalink sp_Print

try this one in cell G5:

=IF(G4="Yes","Issue",IF(AND(G$1>0,$E5-SUM($F5:F5)>0),MIN(SUM($G$1:G1)-SUM($F5:F5),$E5-SUM($F5:F5)),0))

It will relocate only to the next store, the percentage distribution is hard to achieve, much easier with visual basic i think. Or power query.

Avatar
Kevin Konikkara

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
May 25, 2021
sp_UserOfflineSmall Offline
11
May 25, 2021 - 10:26 pm
sp_Permalink sp_Print

Hey I have a similar usecase that would require the use of vlookups

I made a separate thread;

My thread

Please do take a look,

Cheers,

Kevin

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online: Velouria
Guest(s) 93
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 858
Velouria: 580
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 214
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 189
Newest Members:
Vincent Starcevich
Patrick Lawrence
Louise Bell
GIJO GEORGE
Kumud Patel
Atos Franzon
Andrew MacDonald
Artie Ball
Jenn Cain
Peter Vandeweg
Forum Stats:
Groups: 3
Forums: 24
Topics: 6079
Posts: 26684

 

Member Stats:
Guest Posters: 49
Members: 31570
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.