Excel Formula to compare 2 month's price and highlight if there is a change

[ravikiran]

Hi Gurus,

I need some quick help with a formula. I am trying to compare Product prices between few months, where I need to highlight if the price changed from previous month(s). I pasted few scenarios below:

Current month is 202308
Product A - It is exactly the same for both months - so no Query
Product B - It increased by 1 so I have to highlight it to be queried
Product C - There is 2 previous months for comparison. and it is different - so need to be queried
Product D - Price decreased by 1 - need to be queried.

Product	Price	YearMonth	Query
Product A	200	202307
Product A	200	202308
Product B	100	202307
Product B	101	202308	Query
Product C	500	202306
Product C	449	202307
Product C	501	202308	Query
Product D	800	202307
Product D	799	202308	Query

What is the best way to achieve the above? I tried my hand writing a formula and didn't get it to work as expected. I attached the sample excel file with the same data.

Thanks for your help in advance.

Cheers,
Ravi.

 

[ETAF]

maybe a simplier way to do , but i have used

see if that works fro all your scenarios

=IF(AND(COUNTIFS($C$4:$C$12,C4,$D$4:$D$12,D4)<>COUNTIF($C$4:$C$12,C4),MAXIFS($E$4:$E$12,$C$4:$C$12,C4)=E4),"Query","")

if you want you could use in conditional formatting to highlight
AND(COUNTIFS($C$4:$C$12,C4,$D$4:$D$12,$D4)<>COUNTIF($C$4:$C$12,$C4),MAXIFS($E$4:$E$12,$C$4:$C$12,$C4)=$E4),

i have highlighted the cells RED , but could just make bold by changing the formatting

not sure what version of excel you have - so MAXIFS maybe an issue of very old version of excel

 

[ravikiran]

Wow ETAF, that worked perfectly and such a simple logic. I hit myself hard for not thinking that. I just made a minor change to adjust the month automatically as a parameter.

=IF(AND(COUNTIFS($C:$C,C2,$D:$D,D2)<>COUNTIF($C:$C,C2), [@YearMonth]=202208),"Query","")

Can I bother you for another addition, seldom I need to check for those products which differ by a threshold. Sometimes, it might be by 2/5 or even 0.01. Basically I want to highlight only those products which cross that threshold and not those which are within.

Any idea how I might be able to work that out with a formula?

Thanks again for looking into this,
Ravi.

 

[ETAF]

you are welcome