How can I get a google sheet that checks the a price compared to the current price and calculates a % difference based on the set ranges

user_19439901 0
Viewed 55

I'm trying to create a google sheet that compares the current price of items to the new updated price, as a percentage based on the price range. Then outputs this so I can ignore superfluous price changes.

I want to seperate each price into ranges (<=100,>100 <=500, >500) and then check the price difference for each range based on whether it is greater than 10%, 5% and 1% respectively. Then output this as a check box so I can easily sort out the useful updates (I will also use this to update the current price). eg

current price $10.99 new price $10.90 is a 1% price drop this would be superfluous

current price $400 new price $420 is a 10% price rise this is useful

I don't know the syntax for it but something like:

newPrice/currentPrice = diffrence; IF( newPrice >500);  If diffrence > 0.9 or < 1.1 = mark; elseIf(newPrice <= 500 and >100); If diffrence > 0.5 or < 1.05 = mark; elseIf(newPrice <= 100); If diffrence > 0.1 or < 1.01 = mark

This is a mock-up of the sheet

I am wanting to compare the current price with the new price and only mark

  • The prices with a change greater than 10% on values less than $100
  • The prices with a change greater than 5% on vales between $100 and $500
  • The prices with a change greater than 1% on vales greater than $500

So in the above example, the first entry would not be marked but the second one would.

google-sheets
1 Answers

It is not clear to me if you want to sort by current or new price. I understand also that you have two enquiries 1) how to filter by price range and 2) how to filter by drop. In general:

  1. If A1 is your current price and B1 is your new price
  2. Then you can set the price difference (%) in C1 by typing: =(B1-A1)/A1 * 100.
  3. Then you can add something like this in D1:
=if(C1<-10,"HIGH DROP", if(C1<-5,"MEDIUM DROP", if(C1<-1,"SMALL DROP", "INCREASE OR EQUAL")))

This will display:

  • high drop if the drop is greater than 10%
  • medium drop if the drop is between 5% and 10%
  • small drop if the drop is between 1% and 5%
  • increase or equal if there a drop lower than 1% or if the price has increased.
  1. To filter your data, you can select your column header, and click on Data>Create Filter.
  2. You can then select the category you want to filter:

filter of drop

  1. And even add condition to your price range:

filter price range

user_56949830
Related