• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Prorate values according to the code selection criteria

deciog

Active Member
Gentlemen, good morning.

I need formula for version of Excel 2016 and Microsoft 365

Line 3 and 4 are reported data, in example 01 "51300" and "1600" are also reported data, I need a formula that looks at line 4 for added values that are equal to or less than the data in line B, in the case of example 01 the search starts with the cell that corresponds to 51300 and from there it adds up until it gives 1600 (in this case it was exact) ..

In example 2 and 3, for not giving an accurate account, it returns the difference.

I don't know if I was clear enough, but I believe that the examples explain for the best ..

I need the formula to be in line A and B (above) in the attached file it would

I already thank anyone who can help me.
 

Attachments

  • EXAMPLE Decio.xlsx
    10.6 KB · Views: 6
Hi Decio

I have access to some functions from Charles Williams that you will not have. The 365 formula
Code:
= LET(
  runningTotal, ACCUMULATE(IF(sequence>=start,value)),
  runningTotalCapped, IF(runningTotal<cap, runningTotal, cap),
  DIFF(runningTotalCapped, 0) )
gives the results you show.
71528
What I then did was to substitute SUMIFS in place of the more efficient ACCUMULATE and then, in the absence of DIFF, I calculated both opening and closing values for the running totals. This allows me to subtract the totals to return the incremental value.
Code:
= LET(
  runningTotal, SUMIFS(value, sequence, "<="&sequence, sequence, ">="&start),
  runningTotalCapped, IF(runningTotal<cap, runningTotal, cap),
  priorTotal,   SUMIFS(value, sequence, "<"&sequence, sequence, ">="&start),
  priorTotalCapped,   IF(priorTotal<cap, priorTotal, cap),
  runningTotalCapped - priorTotalCapped )
Although I have used LET, your Office2016 implementation could revert to defined names to hold the formulas for 'runningTotal' and 'priorTotal'.
 

Attachments

  • ProratedValues.xlsx
    11.8 KB · Views: 2
Peter, good morning.

It was great, but I also need to put the value (1) where it will be part of a graph and the project, I can't find the formula to make this option

I really appreciate your help, there are always great solutions

I don't have Charles Williams formulas

Decio
 
I have taken a step back towards your original layout to fit the [0,1].
Assuming 'prorated' to be a relative reference to the line below the formula
Code:
= SIGN(prorated#)
and the count
= COUNTIFS(prorated#, ">0")
provides the values shown.
71537
 

Attachments

  • ProratedValues.xlsx
    12.2 KB · Views: 2
Peter

Now it was excellent thank you very much

I still need it for the 2016 version because the company has several terminals with this version

There are more than 200 stores, they haven't changed all yet

Decio
 
Last edited:
@deciog I have shortened the SUMIFS formula in #2 by calculating two offset running totals with an extra array term and using MMULT to difference the two rows
Code:
= LET(
  runningTotals, SUMIFS(value, sequence, {"<=";"<"}&sequence, sequence, ">="&start),
  runningTotalCapped, IF(runningTotals<cap, runningTotals, cap),
  MMULT({1,-1}, runningTotalCapped) )
 
You may like to try this on the Office 2016 machines. I have moved the 'runningTotals' formula to a Defined Name and then used CSE for the MMULT. I suspect that most users will not be able to find the formula within a defined name but then, I guess you don't need your users to check the formula!
 

Attachments

  • ProratedValues.xlsx
    15 KB · Views: 5
Or,

[H7] : =IF($F6<=H$3,MIN(H$4,$F7-SUM($G7:G7)),)

[H6] : =H7 -- >> "Custom Format cells", enter : [>]1;

Or,

[H7] : =($F6<=H$3)*MIN(H$4,$F7-SUM($G7:G7))

>> "Format cells" select "Acccounting" in "Symbol" choose "None"

Regards
 
Last edited:
John Jairo V, Bosco, Good morning.

Great solution works for all versions of Excel

I appreciate the help given

Regards

Decio
 
Last edited:
Interesting. I clearly lost a fair bit of flexibility a few years ago when I decided to permit named references only and use array formulas throughout, so demoting relative referencing to 'methods of last resort'. Still, that was, in part, the intention since complete flexibility includes the unbounded freedom to introduce error. At least I am thankful that arrays no longer involve CSE and the rigid constraints that brings.
 
Back
Top