Excel Formula

TTU_Cas · February 18, 2015, 6:08pm

Don’t judge just started the real world and need help on a function.

I trying to write a formula that relays the following info:

if Between Result

below 0 0

0-5,000 5,000

5,001-10,000 10,0000

10,001- 15,000 15,000

15,001-20,000 20,000

and so on…I would like it to get up to atleast 200,000. I was thinking about building a chart and linking but unsure how to do this.

emichan · February 18, 2015, 6:30pm

So you have a chart that shows the lower and upper limits, and result right? So if you have a:a as lower limit and b:b as upper limit, c:c as result (ie A1=0, B1=5000, C1=5000 etc) Then have D1 be the value you’re inputting And then formula =SUMPRODUCT(–(A1:A41<=D1),–(B1:B41>D1),C1:C41) should spit out the result you need.

rawraw · February 18, 2015, 8:27pm

May be able to use Vlookup as well with a non-exact match. I forget if it rounds up or down though

dwheats · February 18, 2015, 9:17pm

Yea you can do that but it must be listed in chronological order and it uses the closest value below the lookup value i think

TTU_Cas · February 18, 2015, 11:35pm

Thanks for yalls help, I randomly stumbled upon the “ceiling” function which worked perfect for what i was trying to do.

rawraw · February 19, 2015, 1:55pm

I find googling has taught me a lot about several things, including Excel. Don’t know how the world functioned without easily indexing the knowledge of millions. I’ll have to look at the ceiling function

roberto · February 19, 2015, 3:48pm

I love sumproduct.

Twice_the_man · February 20, 2015, 2:49pm

I have 3000 pages of various “For Dummies” and other type books on Excel and VBA sitting on a shelf behind my desk. 99.9% of the time, I google first, google second, google third, then crack a book before ultimatley googling again. It’s always comforting to know that some of the 7 billion other people on earth have encountered your problem, posted their solution, then been told how wrong they are by 4-8 people.

Google has turned me into a mediocre plumber, electrician, tilesetter, carpenter, mechanic, etc. I’ve saved tons of money, wasted lots of time, but I think I come out slightly ahead. Especially with the de-masculinizing of the American man over the last 20 years making most other guys look like total idiots with a wrench or hammer in their hand

rawraw · February 20, 2015, 4:25pm

Twice_the_man:

rawraw:

I find googling has taught me a lot about several things, including Excel. Don’t know how the world functioned without easily indexing the knowledge of millions. I’ll have to look at the ceiling function

I have 3000 pages of various “For Dummies” and other type books on Excel and VBA sitting on a shelf behind my desk. 99.9% of the time, I google first, google second, google third, then crack a book before ultimatley googling again. It’s always comforting to know that some of the 7 billion other people on earth have encountered your problem, posted their solution, then been told how wrong they are by 4-8 people.

Google has turned me into a mediocre plumber, electrician, tilesetter, carpenter, mechanic, etc. I’ve saved tons of money, wasted lots of time, but I think I come out slightly ahead. Especially with the de-masculinizing of the American man over the last 20 years making most other guys look like total idiots with a wrench or hammer in their hand

I think one of my most effective skills is googling. I seem to have a gift for being able to phrase things in the way others would phrase them to get results quickly. But other than having to redo the google search several times, I’ve had similiar experience as you. I even learned how to cook, make pasta, do sushi, etc all via google and youtube lol

roberto · February 22, 2015, 9:50pm

rawraw:

Twice_the_man:

rawraw:

I find googling has taught me a lot about several things, including Excel. Don’t know how the world functioned without easily indexing the knowledge of millions. I’ll have to look at the ceiling function

I have 3000 pages of various “For Dummies” and other type books on Excel and VBA sitting on a shelf behind my desk. 99.9% of the time, I google first, google second, google third, then crack a book before ultimatley googling again. It’s always comforting to know that some of the 7 billion other people on earth have encountered your problem, posted their solution, then been told how wrong they are by 4-8 people.

Google has turned me into a mediocre plumber, electrician, tilesetter, carpenter, mechanic, etc. I’ve saved tons of money, wasted lots of time, but I think I come out slightly ahead. Especially with the de-masculinizing of the American man over the last 20 years making most other guys look like total idiots with a wrench or hammer in their hand

I think one of my most effective skills is googling. I seem to have a gift for being able to phrase things in the way others would phrase them to get results quickly.

+1

And this is a very important skill.

emichan · February 23, 2015, 6:02pm

No joke I had a dinner convo with a friend over the weekend about why in the world I’d ever use Vlookup lol.

Still not convinced. Seriously, why would someone ever use vlookup vs index match? Is it faster?