0

i want to find the percentage of the values in range. for eg : if value between 10 & 20 then display the 20% of value, if value between 20.01 & 50 then display the 10% of value.

a sheet is attached here for more clarification.

https://docs.google.com/spreadsheets/d/1HlIflx8GkoCesl2jWdoHe30PzzAbuXUQE9XGasE7Iys/edit?usp=sharing

Iker666
  • 25
  • 1
  • 5

1 Answers1

0

I have added three sheets to your sample spreadsheet.

The sheet called "Erik Help - Option 1" is self-contained. See the stand-alone array formula in cell B1:

=ArrayFormula({"UPDATED PRICE"; IF(A2:A="","",IFS(A2:A<=7,9.95, A2:A<=10,11.45, A2:A<=20,A2:A*20%, A2:A<=50,A2:A*10%, A2:A,A2:A*5%))})

This formula creates a virtual array by using curly brackets { }. First, the header is placed. Then a simple IF statement checks for whether there is anything in each cell of Column A. If there is not, then Column B will be null; if there is, then Column B will check the value of each cell in Column A against the elements of the IFS function.

The second sheet, entitled "Erik Help - Option 2," relies on the third sheet, entitled "Values." This would allow you more ease in changing assigned values.

The array formula in cell B1 of "Erik Help - Option 2" is as follows:

=ArrayFormula({"UPDATED PRICE"; IF(A2:A="","",IF(VLOOKUP(A2:A,Values!A2:C,3,TRUE)>=1,VLOOKUP(A2:A,Values!A2:C,3,TRUE),A2:A*VLOOKUP(A2:A,Values!A2:C,3,TRUE)))})

This works the same as the previous formula except that instead of referencing IFS for values, this formula references the sheet "Values" with a LOOKUP.

After checking for null values, another IF statement checks to see if the lookup value from Values!C2:C >=1 (i.e., whether it is a dollar amount or a percentage):

If this is TRUE (i.e., if the corresponding Values!C2:C value a dollar amount), the VLOOKUP runs again, comparing values from 'Erik Help - Option 2!'A2:A with the values in Values!A2:A and returning the value in Values!C2:C.

If this is FALSE (i.e., if the corresponding Values!C2:C value is a percentage amount), the values from 'Erik Help - Option 2'!A2:A are multiplied by the corresponding values in Values!C2:C.

As you can see, the results from "Erik Help - Option 1" and "Erik Help - Option 2" are identical. So it comes down to preference. If you want a self-contained option and feel comfortable editing the formula itself in the future if values change, use Option 1. If you prefer the ease of changing the values in chart form, use Option 2 with the Values chart.

NOTE: In my sheets, I also applied currency formatting (Format > Number > Currency) to all cells that contain or might contain dollar values.

Erik Tyler
  • 9,079
  • 2
  • 7
  • 11
  • Thank u man.. u really helped me.. btw can u help me to solve me one more doubt. https://stackoverflow.com/questions/63565184/to-identify-same-words-in-two-tabs-in-a-google-sheet pls take a look – Iker666 Aug 26 '20 at 17:33
  • If the answer I provided addresses your original question, please take a moment to mark it as "Best Answer" so that other contributors know that this issue has been resolved. As for a different issue, please create a new post with that issue. The intention of this site is to make it easy for future users to find solutions to problems, and they won't be able to if questions separate from the original post are added in. I'll try to keep an eye out for the new post. – Erik Tyler Aug 26 '20 at 18:13