petty cash calculator (that aviods circular reference)

[cadence]

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

 

[Yogi Anand]

Hi Cadence:

Following is one way ...

Book2
	A	B	C	D	E	F
1			tax	10%
2
3		Sale	Tax	Total
4	100	100.00	10.00	110.00
5		181.82	18.18	200.00	200
6	181.82	181.82	18.18	200.00
7		100.00	10.00	110.00	110
8		0.00	0.00	0.00
9		0.00	0.00	0.00
10		0.00	0.00	0.00
11		0.00	0.00	0.00
12		0.00	0.00	0.00
13		0	0.00	0
14
Sheet5

Please note to avoid Circular Referencing in Sale and Total Columns, I make entries in other columns (shown colored). in fact columns B, C, and D can be protected, and entries are allowed only in columns A, and B (shown colored)

 

[cadence]

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]

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]

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]

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]

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]

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]

Hi Cadence:

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