Forum Discussion
Which Excel Formula?
Can you help me with which formula would be best for this? I have a standard cost of $2,500 +/-$500 (E2). When I receive the actual cost (H2), I want J2 to show the difference, taking the allowab...
First of all, there is rarely in Excel a single answer to the question "Which formula?" One of the delights of learning Excel is realizing that really all the time there are multiple routes that can take you from Point A to Point B.
One of the factors that shapes picking one approach over another is how you have the various variables present in your spreadsheet. So, for example, does your cell E2 literally have $2,500+/-$500 as the entry? If so, it would be a text field, with its parts needing to be converted to numbers before doing any of that math you describe. The math is easy to set up, in other words; what I would suggest is going to include separate fields for the variances. In the attached spreadsheet, I've also named the fields that contain the variables, so that the formula becomes entirely readable. You could just use the cell references if you prefer. I would not put the actual values into the formula itself--a practice known as "hard-coding" the values, because any change in the standard cost, or the acceptable positive and negative variances requires modifying the formula. Having the ranges named keeps those variables visible and manageable.
=IFERROR(IFS(Actual_Cost>(Std_Cost+PosVar),Actual_Cost-(Std_Cost+PosVar),Actual_Cost<(Std_Cost-NegVar),Actual_Cost-(Std_Cost-NegVar)),"Within Allowable")
The spreadsheet looks like this
Thank you! This is exactly what I was looking for. However, when I try to insert a new line entry below and drag the calculation down, it only pulls from the original line.
That expectation was not a part of your original query. I'm on my way out of my office now, but if you can provide a more complete idea of what your sheet looks like, I or somebody else can help. For example, do the "Standard" and values of the Variances stay the same all the way down, or are those line specific ? If it's possible for you to put a copy of your actual spreadsheet on OneDrive or GoogleDrive with a link here that grants access, we could see exactly what you're working with.
As I noted before, you can change from named ranges to cell references and then make them absolute (e.g., $E$2) or relative (E2) as needed.
Sorry about that! Here's a link of what I'm trying to do..
https://1drv.ms/x/s!AiNfa7lPpKKKg_5mFc_r8PGu_Mvy7Q?e=RNtNd6Edit: I came across one issue and am not sure how to correct it, If the Actual cost is within the variance range of the standard, the answer needs to be 0.
Here you go. Let me know if this works for you. Once again, because those standard cost and variances are standard, there's no reason to repeat them every line. Use absolute references or named range, the latter being what I did here.