LinkBack URL
About LinkBacks
Hi there
Please can you help me with my formula. I've attached the image that says it contains a circular reference. What is wrong with my formula and what can I use to make it work? It works perfectly until I try add -GP amount to the formula.
Hi there
Please can you help me with my formula. I've attached the image that says it contains a circular reference. What is wrong with my formula and what can I use to make it work? It works perfectly until I try add -GP amount to the formula.
Is hard to help if you just upload an image files.
Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
-If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".
-Always upload a workbook before start your question
To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
+++ If my answer(s) helped you, please add me reputation by click on * +++
I can't because the sheet is linked to other sheets etc and it contains confidential information so I can't post the whole book
The formula currently says:
=IF(ISBLANK(D19),"",T19/(1-$S$15))
Now I want to add in -O19 to remove discount?
Then what is your O19 formula?
A workbook can tell more, you can remove those confidential information, just upload a sample will be better.
Or this link can help you?
http://office.microsoft.com/en-us/ex...010342831.aspx
Not sure if this will work but I copied and pasted the sheet into a new workbook....
Basically what I'm asking J19 to do is take cost price, add the 20%GP and then subtract O19 which is the discount amount. But if D19 is blank then it mustn't calculate - what I don't want when I drag the formula down is it to show #VALUE or error values.
Try this if this work for you.
Excel Options > Formulas > Enable Iterative Calculation
In the Calculation options section, select the Enable iterative calculation check box.
To set the maximum number of times that Excel will recalculate, type the number of iterations in the Maximum Iterations box. The higher the number of iterations, the more time that Excel needs to calculate a worksheet.
In the Maximum Change box, type the smallest value required for iteration to continue. The smaller the number, the more precise the result and the more time that Excel needs to calculate a worksheet.
An iterative calculation can have three outcomes:
The solution converges, which means a stable end result is reached. This is the desirable condition.
The solution diverges, which means that from iteration to iteration, the difference between the current and the previous result increases.
The solution switches between two values. For example, after the first iteration the result is 1, after the next iteration the result is 10, after the next iteration the result is 1, and so on.
If you don't wish allow that.
You maybe create another column call Unit Price Discount =J19-O19
Or, calculate the discount the discount in Total column =IF(ISBLANK(D19),"",J19-O19*C19)
Thanks, it sounds a bit complicated. Is there no other way to just make the formula work?
OK thanks let me try that
because your formula is depend each other.
i will suggest you use the solution in post #9.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks