if stement (?) Help

mcommon1

New Member
Joined
Apr 21, 2011
Messages
13
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.

Thanks in advance!
 

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.
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)
 
Upvote 0
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,
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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