petty cash calculator (that aviods circular reference)

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528
Hello all
I am trying to creat a 3 column petty cash table so that no matter if you fill in one column the other two will update automatically.

so my columns are as follows
A= amount (excluding tax)
B = Tax
C = Total including tax

Example
if tax was set to 10%
and I entered in A: amount of $100
then B: Tax would be $10 (A* tax) &
then C: Total would be $110 (A+B)

Alternatively if I enter in the C: total amount of $200
Then A: Amount would be $181.82 calculated on
=(C/((Tax*100)+100))*100
(tax is a percetnage)
then B: would be $18.18 (C-A)

My question is how do I phrase each of the statements so that is does not create a circular reference?

I have been trying IF statements but with no luck as yet.

Can you do IF statements that are only calculated, if a user enters a value? (as a possible solution)

can anyone help

thanks johnny
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528
Thanks Yogi, I am going to try and figure out a way to implement it.

Ideally I want to do it with only three visible columns as there is a presentational element that comes into play.

is there a way round your current metod?

else

Can I do it, so that the additional columns are hidden and the user does not interact with them?

thanks for your feedback.

johnny
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Johnny:

Have a little play with what I have proposed -- get a good feel for it and then let us take it from there.
 

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528

ADVERTISEMENT

yogi , I'm on it as we speak,

I am trying a few ways, all lead to one point that fails into loop.

so now I am trying to have four hidden columns with the tax equation for the original amount in one cell and then what tax is added equation in another cell. then hopefull an If statement on the visible tax side that will select on of these depending on what has been entered in the other cells.

what do you think?

(I also tried basing my equation on on the tax amount to derive what the original amounts were for
ie =Tax amount*(Tax rate%*(100+(G4*100)
& (Tax amount/(Tax rate%))

but it still got in a loop.

see how I go, and advice is welcome.

I understand what you have done, but my presentation needs for users is more important unfortunately.

cheers
 

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528
yogi , I'm on it as we speak,

I am trying a few ways, all lead to one point that fails into loop.

so now I am trying to have four hidden columns with the tax equation for the original amount in one cell and then what tax is added equation in another cell. then hopefull an If statement on the visible tax side that will select on of these depending on what has been entered in the other cells.

what do you think?

(I've also tried basing my equation on on the tax amount to derive what the original amounts were for
ie =Tax amount*(Tax rate%*(100+(Tax Rate*100)
& (Tax amount/(Tax rate%))

but it still got in a loop.

see how I go, and advice is welcome.

I understand what you have done, but my presentation needs for users is more important unfortunately.

cheers
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

Hi Johnny:

If you want a formula based (non-macro) approach without the use of an Add-In, I think you have to use the two additional columns (the colored ones) as I have proposed. You make the entries only in the cells of these two colored columns and the other three columns have formulas that populate the results.

My approach will be to unprotect the cells of the two colored columns, and then protect the sheet -- so a user can make an entry only in the two colored columns and not inadvertently wipe out the formulas in cells of other columns.

If you are willing to use VBA, there may be other options -- but using VBA has its own implications.

Good Luck with your project.
 

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528
I am certainly interested in what you have to offer in way of macros.

With your current approach The difficulty lies in the i canot have five column open or visible to the user. if a user adds to one end of the equation it will not update the other as the calculation is done in an off limits area. so I keep getting stuck when i try it this way.

am happy to send you what I have done it can help see my dilemma.

thanks yogi for your patients.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Cadence:

I have sent you a Private Message ... check your PM Box and then let us take it from there.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,096
Messages
5,545,926
Members
410,713
Latest member
TaremyLunsil
Top