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

tblackwell

New Member
Joined
Oct 24, 2018
Messages
23
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>
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Watch MrExcel Video

Forum statistics

Threads
1,122,165
Messages
5,594,622
Members
413,918
Latest member
Mikey_C

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
Top