[excel] EXCEL Multiple Ranges - need different answers for each range

I have spent a few hours working out how to do this which is why im posting it here now... If you want to return different values in a cell based on which range the value entered in another cell comes under then I have worked out how to do it!! (bear in mind that this is specific to my spreadsheet and was for calculating prices i.e. 0.99 = £0.99)

For example:

  1. IF G2 is ABOVE "0" BUT BELOW "1" THEN display "0.1"
  2. IF G2 is ABOVE "0.99" BUT BELOW "5" THEN display "0.15"
  3. IF G2 is ABOVE "4.99" BUT BELOW "15" THEN display "0.2"
  4. IF G2 is ABOVE "14.99" BUT BELOW "30" THEN display "0.5"
  5. IF G2 is ABOVE "29.99" BUT BELOW "100" THEN display "1.0"
  6. IF G2 is ABOVE "99.99" THEN display "1.30"

So IF G2 was "£18.75" then the cell that this formula is entered in would display "£0.50" based on the value's above.

You will see the above formula contained within the answer to this question below......

This question is related to excel if-statement range spreadsheet

The answer is


use

=VLOOKUP(D4,F4:G9,2)

with the range F4:G9:

0   0.1
1   0.15
5   0.2
15  0.3
30  1
100 1.3

and D4 being the value in question, e.g. 18.75 -> result: 0.3


Nested if's in Excel Are ugly:

=If(G2 < 1, .1, IF(G2 < 5,.15,if(G2 < 15,.2,if(G2 < 30,.5,if(G2 < 100,.1,1.3)))))

That should cover it.


Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to if-statement

How to use *ngIf else? SQL Server IF EXISTS THEN 1 ELSE 2 What is a good practice to check if an environmental variable exists or not? Using OR operator in a jquery if statement R multiple conditions in if statement Syntax for an If statement using a boolean How to have multiple conditions for one if statement in python Ifelse statement in R with multiple conditions If strings starts with in PowerShell Multiple conditions in an IF statement in Excel VBA

Examples related to range

How does String substring work in Swift Creating an Array from a Range in VBA How to create range in Swift? Why is "1000000000000000 in range(1000000000000001)" so fast in Python 3? How to find integer array size in java How does one make random number between range for arc4random_uniform()? Skip over a value in the range function in python Excel Define a range based on a cell value How can I generate a random number in a certain range? Subscript out of range error in this Excel VBA script

Examples related to spreadsheet

How can I quickly and easily convert spreadsheet data to JSON? EXCEL Multiple Ranges - need different answers for each range Excel - find cell with same value in another worksheet and enter the value to the left of it Importing Excel spreadsheet data into another Excel spreadsheet containing VBA Selecting non-blank cells in Excel with VBA