Solved

Formula to calculate outcome based on number range

Posted on 2014-12-22
2
89 Views
Last Modified: 2014-12-22
I have a range of numbers which I need to compare against a table to determine an output. So, for example the table has 7 different options, less than 1000 (equals 1), between 1001-2000 (equals 2), between 2001-3000 (equals 3), etc and so when we input numbers, for example 2431, the output would automatically determine the answer is 3.

I know its a lookup but I dont know how to do a range lookup. I could just do a huge IF formula but I know there is a better way.

I have attached the spreadsheet im working so it is clearer and perhaps the solution can just be put directly into the spreadsheet.

Thanks
Discount-Model.xlsx
0
Comment
Question by:recycleaus
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
You can put the start values of each band into a column and then use those in the LOOKUP formula. See attached version of your workbook. I'm not sure what you expect to happen with the national rate column.
Discount-Model.xlsx
0
 

Author Closing Comment

by:recycleaus
Comment Utility
Perfect... knew it would be an easy solution for someone!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now