# Formula to increase and decrease amount by a percentage found in other cells

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.

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)))))
``````

• Nice Martín +10 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

New Sheet here labeled "Blind"

• 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
Bonus
+/- %
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
_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`