1

I'm trying to create a formula that will reduce and increase a cell value by percentages found in other cells and finding it difficult so hoping someone can help with what i'm trying to do.

I've attached a sample sheet below with comments with what i am trying to achieve, probably super easy but my brain is fried!

if you need any other info please let me know and I'll do my best to provide.

https://docs.google.com/spreadsheets/d/1b-nhNYoamsMr2vFW1YZQvLXLuSksH83iW1weHtDDe3Q/edit#gid=2100307022

Thanks in advance

2 Answers 2

1

You can try this formula for the 5 weeks in just the first cell:

=MAP(B7:B11,BYROW(E7:H11,LAMBDA(e,SUMPRODUCT(e,TRANSPOSE(G31:G34)))),LAMBDA(bonus,perc,bonus*(IF(perc>0,1,SI(perc<-1,0,1+perc)))))

Added in sheet Solution 1

2
  • Nice Martín +10
    – Blindspots
    Jan 18, 2023 at 1:13
  • This was the simplest option to implement and does what i need so i'll mark this as the answer. Thank you though to all that replied. Jan 18, 2023 at 14:39
0

New Sheet here labeled "Blind"

  • Adjusted Bonus => 0
  • Adjusted Bonus =< Gross Bonus

Formulas



Main Formula: $C$5
=BYROW(_audit, 
    LAMBDA(x, 
       OFFSET(INDEX(x,1,1),0,-4)*
       MIN(MAX(1+SUMPRODUCT(
          {rRed, rAmb, rGreen, rGold}*x),0),1)))


Adjusted +/- % Formula: $K$5

Note:   Not needed by Main Formula $C$5 but provides feedback on the calculations being done

=BYROW(_audit, 
    LAMBDA(x,
      {MIN(MAX(SUMPRODUCT({_re, _am, _gr, _go}*x),-1),0),
      SUMPRODUCT({_re, _am, _gr, _go}*x)}))


Total Gross +/- % Formula: $L$10

Note:   Again, not needed by the Main Formula $C$5 but provides feedback on calculations

=SUMPRODUCT(_bonusG*(grossPlusMinus))/SUM(_bonusG)

Sample Tables



Main Table

Note:   Empty columns A, D, J and rows 1-3 were omitted to save space

      B       C           E    F    G   H   I        K        L                            
   4 Gross
Bonus
Adj'd.
Bonus
Re Am Gr Go Adj'd.
+/- %
Gross
+/- %
   5 £200 £100    Wk 1 2 1 1 - 50 % -   50 %
   6 £200 £200    Wk 2 2 ⁠⁠       - +  50 %
   7 £200 £0    Wk 3 4 - 100 % - 200 %
   8 £200 £150    Wk 4 2 2 1 -   25 % -   25 %
   9 £0 £0    Wk 5 ⁠       - ⁠⁠     -
 10 £800 £450   ⇐   Totals    ⇒ 8 3 4 -   44 % -   56 %

Rate Table
Category Change Named Range                                                            
Red - 50 % _re
Amber + 25 % _am
Green + 25 % _gr
Gold      ⁠– _go

Named Ranges
Name Range Description                                                                    
_audit F5:I9 Audit Results Table
_bonusG B5:B9 Gross Bonus Column
_bonusA C5:C9 Adjusted Bonus Column
_re C15 Red +/- %
_am C16 Amber +/- %
_gr C17 Green +/- %
_go C18 Gold +/- %
_gPlusMinus L5:L9 Gross +/- % Bonus Column

Images


Main Formula: $C$5


Adjusted +/- % Formula: $K$5


Total Gross +/- % Formula: $L$10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.