Formula gives me #value

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,992
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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,243
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,243
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,992
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,140,917
Messages
5,703,167
Members
421,279
Latest member
emzy

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