• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with circular reference

C

chssundeep

Member
Hi Excel lovers,

please help me to solve this problem,

I am trying to calculate the amounts using formulas but ending up with an error message as circular reference..

Conditions I have
1. Value A + Value C should be greater than or equal to 10,000 (if equals or lesser than to 10,000 then Value B is zero)
if A+ C is greater than 10,000 the remaining should go to B
if Value B is greater than 35% of Value A then remaining should be added to Value C
if Total is is less than 11,000 Value D is 2% of Total…if Total is greater than 11,000 it is zero
 

Attachments

Hi ,

I think there is some discrepancy in your workbook ; let us consider only the data entered in columns A and C , and try and apply your rules :

The data , starting from row 5 , is as follows :
Code: 
  Value A     Value C
  4,000.00    5,180.00
  4,000.00    6,200.00
  4,000.00    6,225.00
  4,000.00    6,000.00
  4,000.00    6,600.00
  6,000.00   11,900.00
 10,000.00   26,500.00
If we start with the first 2 values , 4000 + 5180 is less than 10000 , so B = 0.

The value of D is 2% of the total , and 2% of 9180 is 183.60 ; you have put in 180. Why ?

For the next row , 4000 + 6200 is greater than 10000 , so the excess above 10000 , which is 200 in this case should go to B ; you are showing 0. Why ?

However , since the total is less than 11000 , 2% of the total goes to D , which is 204 ; you have put in 200. This is subtracted from C , which means B is 0. Let us assume this.

For the next row , 4000 + 6225 is 10225 , but you have put in 11225. Why ?

I think you need to explain in more detail.

Narayan
 
Dear Narayan,

Thanks you very much for the reply and sorry for not being clear...

As pointed out by you the 2% values which I have entered in Value D were wrong...

Here the Amount & Value A is what I get....The Net amount (after all calculations) should be equal to Amount

The conditions are

1. If Total is less than 11,000 Value D is 2% else 0

2. Value D amount is dedcuted from Value C if Value B is zero else it is decuted from Value B

3. if Value A+ Value C is greater than 10,000 the remaining should go to Value B else Value B is zero

4. if Value B is greater than 35% of Value A then remaining should be added to Value C


Please help me Narayan,,if still I am not clear please ask me
 
Narayan,

Sorry for the late reply....

Thank you very much for the solution......This have saved me a lot of time.......

Once again I thank you very much
 
You must log in or register to reply here.
Back
Top