Forum Discussion
Returning values that are over £250 or lower than -£250 in Column S if Column R has relevant data
Hi all,
I've been trying to fix a formula for ages now and I can't seem to get the results I need. I'm doing some discrepancy analysis and I want to get an accurate picture for those that solely higher than £250 and higher than -£250, but only if the column next to it has data that is higher than £0 or higher than -£1. In other words, if column A has £350, but then the column B next to it has £600, I want it to be counted as 1 discrepancy that is over £250. Or, vice versa, if column A has £250, but then column B has -£600, I want it to be counted as 1 discrepancy that is higher than -£250.
Simplest formula I thought of was COUNTIFS and base it upon branch criteria. I tried this, but then the values I get in my table differ to the actual count in the raw data. For e.g. it may return an overall count of 3 discrepancies that are higher than £250, or higher than -£250, but raw data actually states there are 4 discrepancies that are higher than £250, and so on and so forth.
Can somebody help please? Am I missing the obvious (which I think I am to be honest)?
Thank you very much!!
Pedro M.
9 Replies
pedrohrmoreira Perhaps you mean something like in the attached file? If not, kindly ignore.
Riny_van_Eekelen I tried this but this will work for "overage" discrepancies over £250, but if there is a "shortage" discrepancy of -£250 or less, they will not be counted. Please advise if I'm incorrect?
pedrohrmoreira I used the ABS function to avoid plus or minus. The second row in my example has values 260 and -600. The absolute variance between the two equals 860, which is greater than 250. So, it doesn't matter if the variance is -860 or 860.
Pedro, what do you mean under higher? Every number which is higher than +250 is also higher than -250. -100 is higher than -250 and -500 is less than -250.
Hi Sergei,
Sorry, I wrote that completely incorrectly. Let me explain it again and hopefully this time without making it sound confusing.
For e.g. Column A is what the value should be based on paperwork and Column B has the actual value. If Column A has a value of £1,000 and then Column B has another value of £1,500, then this means we have an "overage" discrepancy of £500, meaning it is over our £250 threshold. I would therefore want my formula to return this as 1 count of discrepancy that is over the £250 threshold.
I would also want the formula to return a "shortage" discrepancy that is less than the -£250 threshold (i.e. if the discrepancy was -£500 based upon Column A having £0, but Column B having -£500).
I hope this explains it better?
