How do I multiply a negative number by a positive factor and get a less negative number?

T

tblackwell

New Member
Joined
Oct 24, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows
Hi - Newbie here. Read the FAQs, rules and searched the forum and did not find an answer so created a new post. Bear with me if I make a mistake.

Question: How do I multiply a negative number by a positive factor and get a less negative number?
Hypothetical Example: -2.0 * 1.1 = -2.2. But what I want is: -2.0 * 1.1 = -1.8.
Real Example:
  • In the excel screen shot below, AO40 is -2.36%.
  • This -2.36% is then adjusted by factors in BI, BM, BQ & BS.
  • For example, for Salt Lake the formula in AO24 is: =(AO$40*$BI$24)*$BM$24*$BQ$24*$BS$24
  • Result = -3.11%
  • This works beautifully is AO40 is a positive number.
  • But AO40 is a negative number!
  • So what I want is AO24 to be less negative than -2.36%
  • How do I write the formula for this?
Current formula where I get stuck: =IF(AO$40>0,(AO$40*$BI$24)*$BM$24*$BQ$24*$BS$24,_____________________)
Version: Office 365
System: Windows 10 Pro


AJAKALAMANAOAPAQARASATAUAVAWAXAYAZBDBIBMBQBS
19
20JanFebMarchAprilMayJuneJulyAugustSeptOctNovDec
21P1 P2 P3 P4 P5 P6 P7 P8 P9 P10 P11 P12 Total
22Pop Factor AdjSales Factor AdjShare Factor AdjOps Factor Adj
24Salt Lake -0.45%1.70%3.28%2.66%-3.11%-0.04%1.94%3.77%5.14%2.52%3.17%2.40%1.90%Salt Lake 1.21.01.01.1
25Kansas City-0.45%1.68%3.25%2.39%-2.80%-0.04%1.92%3.74%5.09%2.50%3.14%2.38%1.88%Kansas City1.11.20.91.1
26Greensboro-0.41%1.54%2.98%2.20%-2.57%-0.03%1.44%2.80%3.82%1.87%2.35%1.78%1.47%Greensboro0.91.11.10.9
27Raleigh-0.55%2.04%3.94%2.90%-3.39%-0.04%2.11%4.12%5.61%2.75%3.46%2.62%2.11%Raleigh1.21.21.01.0
28Allentown-0.29%1.10%2.12%1.56%-1.82%-0.03%1.14%2.21%3.02%1.48%1.86%1.41%1.13%Allentown0.80.81.11.0
29Harrisburg-0.41%1.54%2.98%2.20%-2.57%-0.03%1.76%3.42%4.67%2.29%2.88%2.18%1.72%Harrisburg0.91.11.11.1
30Baltimore North-0.27%1.02%1.97%1.45%-1.70%-0.02%1.06%2.06%2.80%1.38%1.73%1.31%1.05%Baltimore North0.80.91.01.0
31Central Maryland-0.30%1.13%2.19%1.61%-1.88%-0.02%1.06%2.06%2.80%1.38%1.73%1.31%1.08%Central Maryland1.00.81.00.9
32Baltimore South-0.34%1.28%2.46%1.81%-2.12%-0.03%1.19%2.32%3.16%1.55%1.95%1.47%1.21%Baltimore South1.00.91.00.9
33
34
36
40-0.38%1.42%2.73%2.02%-2.36%-0.03%1.47%2.86%3.90%1.91%2.40%1.82%1.46%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Summary​

Worksheet Formulas
CellFormula
AJ24=B24
AK24=(AK$40*$BI$24)*$BM$24*$BQ$24
AL24=(AL$40*$BI$24)*$BM$24*$BQ$24
AM24=(AM$40*$BI$24)*$BM$24*$BQ$24
AN24=(AN$40*$BI$24)*$BM$24*$BQ$24*$BS$24
AO24=(AO$40*$BI$24)*$BM$24*$BQ$24*$BS$24
AP24=(AP$40*$BI$24)*$BM$24*$BQ$24*$BS$24
AQ24=(AQ$40*$BI$24)*$BM$24*$BQ$24*$BS$24
AR24=(AR$40*$BI$24)*$BM$24*$BQ$24*$BS$24
AS24=(AS$40*$BI$24)*$BM$24*$BQ$24*$BS$24
AT24=(AT$40*$BI$24)*$BM$24*$BQ$24*$BS$24
AU24=(AU$40*$BI$24)*$BM$24*$BQ$24*$BS$24
AV24=(AV$40*$BI$24)*$BM$24*$BQ$24*$BS$24
AJ25=B25
AK25=(AK$40*$BI$25)*$BM$25*$BQ$25
AL25=(AL$40*$BI$25)*$BM$25*$BQ$25
AM25=(AM$40*$BI$25)*$BM$25*$BQ$25
AN25=(AN$40*$BI$25)*$BM$25*$BQ$25
AO25=(AO$40*$BI$25)*$BM$25*$BQ$25
AP25=(AP$40*$BI$25)*$BM$25*$BQ$25
AQ25=(AQ$40*$BI$25)*$BM$25*$BQ$25*$BS$25
AR25=(AR$40*$BI$25)*$BM$25*$BQ$25*$BS$25
AS25=(AS$40*$BI$25)*$BM$25*$BQ$25*$BS$25
AT25=(AT$40*$BI$25)*$BM$25*$BQ$25*$BS$25
AU25=(AU$40*$BI$25)*$BM$25*$BQ$25*$BS$25
AV25=(AV$40*$BI$25)*$BM$25*$BQ$25*$BS$25
AJ26=B26
AK26=(AK$40*$BI$26)*$BM$26*$BQ$26
AL26=(AL$40*$BI$26)*$BM$26*$BQ$26
AM26=(AM$40*$BI$26)*$BM$26*$BQ$26
AN26=(AN$40*$BI$26)*$BM$26*$BQ$26
AO26=(AO$40*$BI$26)*$BM$26*$BQ$26
AP26=(AP$40*$BI$26)*$BM$26*$BQ$26
AQ26=(AQ$40*$BI$26)*$BM$26*$BQ$26*$BS$26
AR26=(AR$40*$BI$26)*$BM$26*$BQ$26*$BS$26
AS26=(AS$40*$BI$26)*$BM$26*$BQ$26*$BS$26
AT26=(AT$40*$BI$26)*$BM$26*$BQ$26*$BS$26
AU26=(AU$40*$BI$26)*$BM$26*$BQ$26*$BS$26
AV26=(AV$40*$BI$26)*$BM$26*$BQ$26*$BS$26
AJ27=B27
AK27=(AK$40*$BI$27)*$BM$27*$BQ$27
AL27=(AL$40*$BI$27)*$BM$27*$BQ$27
AM27=(AM$40*$BI$27)*$BM$27*$BQ$27
AN27=(AN$40*$BI$27)*$BM$27*$BQ$27
AO27=(AO$40*$BI$27)*$BM$27*$BQ$27
AP27=(AP$40*$BI$27)*$BM$27*$BQ$27
AQ27=(AQ$40*$BI$27)*$BM$27*$BQ$27*$BS$27
AR27=(AR$40*$BI$27)*$BM$27*$BQ$27*$BS$27
AS27=(AS$40*$BI$27)*$BM$27*$BQ$27*$BS$27
AT27=(AT$40*$BI$27)*$BM$27*$BQ$27*$BS$27
AU27=(AU$40*$BI$27)*$BM$27*$BQ$27*$BS$27
AV27=(AV$40*$BI$27)*$BM$27*$BQ$27*$BS$27
AJ28=B28
AK28=(AK$40*$BI$28)*$BM$28*$BQ$28*$BU$28
AL28=(AL$40*$BI$28)*$BM$28*$BQ$28*$BU$28
AM28=(AM$40*$BI$28)*$BM$28*$BQ$28*$BU$28
AN28=(AN$40*$BI$28)*$BM$28*$BQ$28*$BU$28
AO28=(AO$40*$BI$28)*$BM$28*$BQ$28*$BU$28
AP28=(AP$40*$BI$28)*$BM$28*$BQ$28*$BU$28*$BU$28
AQ28=(AQ$40*$BI$28)*$BM$28*$BQ$28*$BS$28*$BU$28
AR28=(AR$40*$BI$28)*$BM$28*$BQ$28*$BS$28*$BU$28
AS28=(AS$40*$BI$28)*$BM$28*$BQ$28*$BS$28*$BU$28
AT28=(AT$40*$BI$28)*$BM$28*$BQ$28*$BS$28*$BU$28
AU28=(AU$40*$BI$28)*$BM$28*$BQ$28*$BS$28*$BU$28
AV28=(AV$40*$BI$28)*$BM$28*$BQ$28*$BS$28*$BU$28
AJ29=B29
AK29=(AK$40*$BI$29)*$BM$29*$BQ$29
AL29=(AL$40*$BI$29)*$BM$29*$BQ$29
AM29=(AM$40*$BI$29)*$BM$29*$BQ$29
AN29=(AN$40*$BI$29)*$BM$29*$BQ$29
AO29=(AO$40*$BI$29)*$BM$29*$BQ$29
AP29=(AP$40*$BI$29)*$BM$29*$BQ$29
AQ29=(AQ$40*$BI$29)*$BM$29*$BQ$29*$BS$29
AR29=(AR$40*$BI$29)*$BM$29*$BQ$29*$BS$29
AS29=(AS$40*$BI$29)*$BM$29*$BQ$29*$BS$29
AT29=(AT$40*$BI$29)*$BM$29*$BQ$29*$BS$29
AU29=(AU$40*$BI$29)*$BM$29*$BQ$29*$BS$29
AV29=(AV$40*$BI$29)*$BM$29*$BQ$29*$BS$29
AJ30=B30
AK30=(AK$40*$BI$30)*$BM$30*$BQ$30
AL30=(AL$40*$BI$30)*$BM$30*$BQ$30
AM30=(AM$40*$BI$30)*$BM$30*$BQ$30
AN30=(AN$40*$BI$30)*$BM$30*$BQ$30
AO30=(AO$40*$BI$30)*$BM$30*$BQ$30
AP30=(AP$40*$BI$30)*$BM$30*$BQ$30
AQ30=(AQ$40*$BI$30)*$BM$30*$BQ$30*$BS$30
AR30=(AR$40*$BI$30)*$BM$30*$BQ$30*$BS$30
AS30=(AS$40*$BI$30)*$BM$30*$BQ$30*$BS$30
AT30=(AT$40*$BI$30)*$BM$30*$BQ$30*$BS$30
AU30=(AU$40*$BI$30)*$BM$30*$BQ$30*$BS$30
AV30=(AV$40*$BI$30)*$BM$30*$BQ$30*$BS$30
AJ31=B31
AK31=(AK$40*$BI$31)*$BM$31*$BQ$31
AL31=(AL$40*$BI$31)*$BM$31*$BQ$31
AM31=(AM$40*$BI$31)*$BM$31*$BQ$31
AN31=(AN$40*$BI$31)*$BM$31*$BQ$31
AO31=(AO$40*$BI$31)*$BM$31*$BQ$31
AP31=(AP$40*$BI$31)*$BM$31*$BQ$31
AQ31=(AQ$40*$BI$31)*$BM$31*$BQ$31*$BS$31
AR31=(AR$40*$BI$31)*$BM$31*$BQ$31*$BS$31
AS31=(AS$40*$BI$31)*$BM$31*$BQ$31*$BS$31
AT31=(AT$40*$BI$31)*$BM$31*$BQ$31*$BS$31
AU31=(AU$40*$BI$31)*$BM$31*$BQ$31*$BS$31
AV31=(AV$40*$BI$31)*$BM$31*$BQ$31*$BS$31
AJ32=B32
AK32=(AK$40*$BI$32)*$BM$32*$BQ$32
AL32=(AL$40*$BI$32)*$BM$32*$BQ$32
AM32=(AM$40*$BI$32)*$BM$32*$BQ$32
AN32=(AN$40*$BI$32)*$BM$32*$BQ$32
AO32=(AO$40*$BI$32)*$BM$32*$BQ$32
AP32=(AP$40*$BI$32)*$BM$32*$BQ$32
AQ32=(AQ$40*$BI$32)*$BM$32*$BQ$32*$BS$32
AR32=(AR$40*$BI$32)*$BM$32*$BQ$32*$BS$32
AS32=(AS$40*$BI$32)*$BM$32*$BQ$32*$BS$32
AT32=(AT$40*$BI$32)*$BM$32*$BQ$32*$BS$32
AU32=(AU$40*$BI$32)*$BM$32*$BQ$32*$BS$32
AV32=(AV$40*$BI$32)*$BM$32*$BQ$32*$BS$32
AX24=((AK24*5)+(AL24*4)+(AM24*4)+(AN24*5)+(AO24*4)+(AP24*4)+(AQ24*5)+(AR24*4)+(AS24*4)+(AT24*5)+(AU24*4)+(AV24*PriorP12Wks))/PriorYrWks
AX25=((AK25*5)+(AL25*4)+(AM25*4)+(AN25*5)+(AO25*4)+(AP25*4)+(AQ25*5)+(AR25*4)+(AS25*4)+(AT25*5)+(AU25*4)+(AV25*PriorP12Wks))/PriorYrWks
AX26=((AK26*5)+(AL26*4)+(AM26*4)+(AN26*5)+(AO26*4)+(AP26*4)+(AQ26*5)+(AR26*4)+(AS26*4)+(AT26*5)+(AU26*4)+(AV26*PriorP12Wks))/PriorYrWks
AX27=((AK27*5)+(AL27*4)+(AM27*4)+(AN27*5)+(AO27*4)+(AP27*4)+(AQ27*5)+(AR27*4)+(AS27*4)+(AT27*5)+(AU27*4)+(AV27*PriorP12Wks))/PriorYrWks
AX28=((AK28*5)+(AL28*4)+(AM28*4)+(AN28*5)+(AO28*4)+(AP28*4)+(AQ28*5)+(AR28*4)+(AS28*4)+(AT28*5)+(AU28*4)+(AV28*PriorP12Wks))/PriorYrWks
AX29=((AK29*5)+(AL29*4)+(AM29*4)+(AN29*5)+(AO29*4)+(AP29*4)+(AQ29*5)+(AR29*4)+(AS29*4)+(AT29*5)+(AU29*4)+(AV29*PriorP12Wks))/PriorYrWks
AX30=((AK30*5)+(AL30*4)+(AM30*4)+(AN30*5)+(AO30*4)+(AP30*4)+(AQ30*5)+(AR30*4)+(AS30*4)+(AT30*5)+(AU30*4)+(AV30*PriorP12Wks))/PriorYrWks
AX31=((AK31*5)+(AL31*4)+(AM31*4)+(AN31*5)+(AO31*4)+(AP31*4)+(AQ31*5)+(AR31*4)+(AS31*4)+(AT31*5)+(AU31*4)+(AV31*PriorP12Wks))/PriorYrWks
AX32=((AK32*5)+(AL32*4)+(AM32*4)+(AN32*5)+(AO32*4)+(AP32*4)+(AQ32*5)+(AR32*4)+(AS32*4)+(AT32*5)+(AU32*4)+(AV32*PriorP12Wks))/PriorYrWks
BI24=IF(BH24>$BH$49,"1.2",IF(BH24>$BH$48,"1.1",IF(BH24>$BH$47,"1.0",IF(BH24>$BH$46,"0.9","0.8"))))
BI25=IF(BH25>$BH$49,"1.2",IF(BH25>$BH$48,"1.1",IF(BH25>$BH$47,"1.0",IF(BH25>$BH$46,"0.9","0.8"))))
BI26=IF(BH26>$BH$49,"1.2",IF(BH26>$BH$48,"1.1",IF(BH26>$BH$47,"1.0",IF(BH26>$BH$46,"0.9","0.8"))))
BI27=IF(BH27>$BH$49,"1.2",IF(BH27>$BH$48,"1.1",IF(BH27>$BH$47,"1.0",IF(BH27>$BH$46,"0.9","0.8"))))
BI28=IF(BH28>$BH$49,"1.2",IF(BH28>$BH$48,"1.1",IF(BH28>$BH$47,"1.0",IF(BH28>$BH$46,"0.9","0.8"))))
BI29=IF(BH29>$BH$49,"1.2",IF(BH29>$BH$48,"1.1",IF(BH29>$BH$47,"1.0",IF(BH29>$BH$46,"0.9","0.8"))))
BI30=IF(BH30>$BH$49,"1.2",IF(BH30>$BH$48,"1.1",IF(BH30>$BH$47,"1.0",IF(BH30>$BH$46,"0.9","0.8"))))
BI31=IF(BH31>$BH$49,"1.2",IF(BH31>$BH$48,"1.1",IF(BH31>$BH$47,"1.0",IF(BH31>$BH$46,"0.9","0.8"))))
BI32=IF(BH32>$BH$49,"1.2",IF(BH32>$BH$48,"1.1",IF(BH32>$BH$47,"1.0",IF(BH32>$BH$46,"0.9","0.8"))))
BM24=IF(BL24>BL$49,"0.8",IF(BL24>BL$48,"0.9",IF(BL24>BL$47,"1.0",IF(BL24>BL$46,"1.1","1.2"))))
BM25=IF(BL25>BL$49,"0.8",IF(BL25>BL$48,"0.9",IF(BL25>BL$47,"1.0",IF(BL25>BL$46,"1.1","1.2"))))
BM26=IF(BL26>BL$49,"0.8",IF(BL26>BL$48,"0.9",IF(BL26>BL$47,"1.0",IF(BL26>BL$46,"1.1","1.2"))))
BM27=IF(BL27>BL$49,"0.8",IF(BL27>BL$48,"0.9",IF(BL27>BL$47,"1.0",IF(BL27>BL$46,"1.1","1.2"))))
BM28=IF(BL28>BL$49,"0.8",IF(BL28>BL$48,"0.9",IF(BL28>BL$47,"1.0",IF(BL28>BL$46,"1.1","1.2"))))
BM29=IF(BL29>BL$49,"0.8",IF(BL29>BL$48,"0.9",IF(BL29>BL$47,"1.0",IF(BL29>BL$46,"1.1","1.2"))))
BM30=IF(BL30>BL$49,"0.8",IF(BL30>BL$48,"0.9",IF(BL30>BL$47,"1.0",IF(BL30>BL$46,"1.1","1.2"))))
BM31=IF(BL31>BL$49,"0.8",IF(BL31>BL$48,"0.9",IF(BL31>BL$47,"1.0",IF(BL31>BL$46,"1.1","1.2"))))
BM32=IF(BL32>BL$49,"0.8",IF(BL32>BL$48,"0.9",IF(BL32>BL$47,"1.0",IF(BL32>BL$46,"1.1","1.2"))))
BQ24=IF(BP24>0.2%,"1.1",IF(BP24>-0.001%,"1.0","0.9"))
BQ25=IF(BP25>0.2%,"1.1",IF(BP25>-0.001%,"1.0","0.9"))
BQ26=IF(BP26>0.2%,"1.1",IF(BP26>-0.001%,"1.0","0.9"))
BQ27=IF(BP27>0.2%,"1.1",IF(BP27>-0.001%,"1.0","0.9"))
BQ28=IF(BP28>0.2%,"1.1",IF(BP28>-0.001%,"1.0","0.9"))
BQ29=IF(BP29>0.2%,"1.1",IF(BP29>-0.001%,"1.0","0.9"))
BQ30=IF(BP30>0.2%,"1.1",IF(BP30>-0.001%,"1.0","0.9"))
BQ31=IF(BP31>0.2%,"1.1",IF(BP31>-0.001%,"1.0","0.9"))
BQ32=IF(BP32>0.2%,"1.1",IF(BP32>-0.001%,"1.0","0.9"))
BS24=IF(BR24>$BR$48,"1.1",IF(BR24>$BR$47,"1.0","0.9"))
BS25=IF(BR25>$BR$48,"1.1",IF(BR25>$BR$47,"1.0","0.9"))
BS26=IF(BR26>$BR$48,"1.1",IF(BR26>$BR$47,"1.0","0.9"))
BS27=IF(BR27>$BR$48,"1.1",IF(BR27>$BR$47,"1.0","0.9"))
BS28=IF(BR28>$BR$48,"1.1",IF(BR28>$BR$47,"1.0","0.9"))
BS29=IF(BR29>$BR$48,"1.1",IF(BR29>$BR$47,"1.0","0.9"))
BS30=IF(BR30>$BR$48,"1.1",IF(BR30>$BR$47,"1.0","0.9"))
BS31=IF(BR31>$BR$48,"1.1",IF(BR31>$BR$47,"1.0","0.9"))
BS32=IF(BR32>$BR$48,"1.1",IF(BR32>$BR$47,"1.0","0.9"))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Worksheet Defined Names
NameRefers To
Summary!PriorP12Wks=Summary!$E$6
Summary!PriorYrWks=Summary!$D$6

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
You must log in or register to reply here.

Similar threads

C
  • Question
Forecast Function - Negative Values
Replies
1
Views
184
etaf
etaf
P
  • Solved
Issues with negative numbers in Polynomial Trendline Equation
Replies
3
Views
191
PerezM
P
N
  • Locked
  • Question
Exccel - date formula that drives multiple other formulas
Replies
2
Views
171
Fluff
Fluff
F
  • Question
How to multiply two arrays together
Replies
1
Views
144
FlashDota
F
L
  • Solved
Formula to Find the Average of Negative Time
Replies
6
Views
472
pjmorris
P

Forum statistics

Threads
1,215,517
Messages
6,125,287
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top