Formula gives me #value

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,998
Office Version
  1. 2019
Platform
  1. Windows
I have a formula in Col Q =IF(AND(M2="",O2="","",ROUND(L2+N2,0)),ROUND(L2+O2,0)) which gives me #value


I have basic salaries in Col L from row 2 onwards



I can either Input a % increase in Col M and the increased value will be computed in Col N or I can input a $ value in Col O and the % increase will be computed in Col P

I need to compute the new salary on either the Value in Col N if calculated or Col O if manually inputted



It would be apreciated if someone can please assist me


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">CURR BASIC</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">% Inc</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;"> Value (Calc)</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;"> Value (Input)</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">% inc Calc</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">New Sal</td><td style="border-left: 1px solid black;;">Should Be</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-right: 1px solid black;;">8150</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">5.00%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">407.50</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">#VALUE!</td><td style="text-align: right;border-left: 1px solid black;;">8,557.50</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-right: 1px solid black;;">3000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">500.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">16.67%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">#VALUE!</td><td style="text-align: right;border-left: 1px solid black;;">3,500.00</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-right: 1px solid black;;">7850</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #DAEEF3;;">6.00%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">471.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #DAEEF3;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">#VALUE!</td><td style="text-align: right;border-left: 1px solid black;;">8,321.00</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">P2</th><td style="text-align:left">=IF(<font color="Blue">O2="","",O2/L2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">Q2</th><td style="text-align:left">=N1=IF(<font color="Blue">AND(<font color="Red">M2="",O2="","",ROUND(<font color="Green">L2+N2,0</font>)</font>),ROUND(<font color="Red">L2+O2,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">R2</th><td style="text-align:left">=+L2+N2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">P3</th><td style="text-align:left">=IF(<font color="Blue">O3="","",O3/L3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">Q3</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">M3="",O3="","",ROUND(<font color="Green">L3+N3,0</font>)</font>),ROUND(<font color="Red">L3+O3,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">R3</th><td style="text-align:left">=+L3+O3</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">P4</th><td style="text-align:left">=IF(<font color="Blue">O4="","",O4/I3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">Q4</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">M4="",O4="","",ROUND(<font color="Green">L4+N4,0</font>)</font>),ROUND(<font color="Red">L4+O4,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">R4</th><td style="text-align:left">=+L4+N4</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">N2</th><td style="text-align:left">=IF(<font color="Blue">M2="","",L2*M2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">N3</th><td style="text-align:left">=IF(<font color="Blue">M3="","",L3*M3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">N4</th><td style="text-align:left">=IF(<font color="Blue">M4="","",L4*M4</font>)</td></tr></tbody></table></td></tr></table><br />
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
Try:
Code:
=IFERROR(ROUND(L2+IF(Len(M2)=0,N2,O2),0),L2)
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,244
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this in Q2 and copy down

=if(isblank(P2),L2+N2,L2+O2)
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,244
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
btw, 16.67% inc!
are you hiring?
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,998
Office Version
  1. 2019
Platform
  1. Windows
Thanks for your input, much appreciated

BTW Alan unfortunately this is only fictitious data
 

Forum statistics

Threads
1,144,615
Messages
5,725,321
Members
422,613
Latest member
salim9696

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