# if stement (?) Help

#### mcommon1

##### New Member
I am doing a salary page. column A is current salary, column B is % increase, column C is \$ increase. I would like to put in a % and calculated value for B & C (say 5% and the corresponding \$ amount) as a budget. I would like the users to be able to input either a % change in column B (where C would calculate off of A) OR a \$ change in column C where B would calculate off of column A. Seems to be simple but I'm stuck.

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Would this work?
=A2*(1+B2)+C2

Excel Workbook
ABCD
1Original%\$New Amount
250005%5250
35000155015
Sheet1

Well, you can't have both a formula and a value in one cell.

My suggestion would be to have input % or \$ in columns B and C and calculated % or \$ in columns D and E.

Suppose data start in row 2, enter in D2 and copy down something like (depending on your rules for rounding)
Code:
``=IF(B2<>"",B2,ROUND(C2/A2,2))``

In E2 and copy down:
Code:
``=IF(B2<>"",ROUND(A2*(1+B2),0),C2)``

Sorry, I already am showing output columns D & E with New rate and % increase
Column B Column C Column D* Column E*
Increase on a % basis \$ increase (annual for Exempt, per hour for non-exempt H/S) New Rate % Increase
3.50% \$0.62 \$18.25 3.50%

Where column D & E are calc'ed of of (A+C) and (C/A) respectively... I can use another cell or 2 to move to keep from circular refs... Make sense? Maybe not doable...

Best regards,

Sorry, I'm a bit puzzled now.
Sure you should adapt the proposed formulas according to your specific requirements.
So will Scott's and/or my suggestions be enough for you to solve your issue or is anything still left to be answered?

Sorry, I'm a bit puzzled now.
Sure you should adapt the proposed formulas according to your specific requirements.
So will Scott's and/or my suggestions be enough for you to solve your issue or is anything still left to be answered?

Sorry, I'm just trying to get around a circular, and allow input to either B or C that would allow for the other to process correctly... I can use other cells if I need to but the end result for b or c needs to have a formula at first, but if overwritten allow the other formulae to process correctly (so they can input a % change - overwriting the formula in that cell) and the other columns will update properly OR a \$ amount - (overwriting the formula in THAT cell) and the others will calculate... probably not possible.

OK, clear.
As you mention “circular”, it looks like you already tried something like the code below, which is not working due to…

Circular referencing:
Code:
``````In B2 and copied down: =IF(C2="","",IFERROR(C2/A2,"Invalid amount"))
In C2 and copied down: =IF(B2="","",IFERROR(ROUND(A2*B2,2),"Invalid %"))``````
But there are working alternatives.

Excel 2013 solution:
Code:
``````In B2 and copied down: =IF(ISFORMULA(C2),"",IFERROR(C2/A2,"Invalid amount"))
In C2 and copied down: =IF(ISFORMULA(B2),"",IFERROR(ROUND(A2*B2,2),"Invalid %"))``````

Lower Excel versions workaround:
You need helper columns whereever you have columns available, let’s say users enter a % in column Y or an amount in column Z.
Code:
``````In B2 and copied down: =IF(Z2<>"",IFERROR(Z2/A2,"Invalid amount"),IF(Y2<>"",Y2,""))
In C2 and copied down: =IF(Y2<>"",IFERROR(ROUND(A2*Y2,2),"Invalid %"),IF(Z2<>"",Z2,""))``````

Last edited:

Replies
0
Views
346
Replies
2
Views
797
Replies
1
Views
409
Replies
4
Views
310
Replies
0
Views
372

### Forum statistics

1,196,448
Messages
6,015,329
Members
441,889
Latest member
balolaptopgiaolong

### 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.

### Which adblocker are you using?

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

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