Prorate values according to the code selection criteria

[deciog]

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.

 

[Peter Bartholomew]

Hi Decio

I have access to some functions from Charles Williams that you will not have. The 365 formula

= LET(
  runningTotal, ACCUMULATE(IF(sequence>=start,value)),
  runningTotalCapped, IF(runningTotal<cap, runningTotal, cap),
  DIFF(runningTotalCapped, 0) )

gives the results you show.

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.

= 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'.

 

[deciog]

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

 

[Peter Bartholomew]

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

= SIGN(prorated#)
and the count
= COUNTIFS(prorated#, ">0")

provides the values shown.

 

[deciog]

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

 

[Peter Bartholomew]

@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

= LET(
  runningTotals, SUMIFS(value, sequence, {"<=";"<"}&sequence, sequence, ">="&start),
  runningTotalCapped, IF(runningTotals<cap, runningTotals, cap),
  MMULT({1,-1}, runningTotalCapped) )

 

[deciog]

It was great

Decio

greetings

 

[Peter Bartholomew]

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!

 

[deciog]

Peter

Wow, it was great, thank you very much

Decio

 

[John Jairo V]

Hi to both!

Another option could be:
[H7] : =IF(COUNTIF($H$3:H$3,$F6),MIN(H$4,$F7-SUM($G7:G7)),)
[H6] : =--(H7>0)

Blessings!

 

[bosco_yip]

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

 

[deciog]

John Jairo V, Bosco, Good morning.

Great solution works for all versions of Excel

I appreciate the help given

Regards

Decio

 

[Peter Bartholomew]

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.