Returning values that are over £250 or lower than -£250 in Column S if Column R has relevant data

Copper Contributor

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 

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.

@pedrohrmoreira Perhaps you mean something like in the attached file? If not, kindly ignore.

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?

@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 

You can solve this by using formula:

=IF(C2>250,"Overage",IF(C2<-250,"Shortage",0))

@pedrohrmoreira 

If you have OR condition, when you may use =COUNTIF()+COUNTIF()  (or SUMPRODUCT() )

@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.

@Riny_van_Eekelen thanks. It's just my organisation wanted it displayed as overages over £250 vs. shortages of less than -£250. But if I were to use your example as a workaround, how could I merge it with a COUNTIF so that I could have the discrepancy values by branch a, branch b, etc?

@pedrohrmoreira Revised the schedule a little bit. Please see attached.