PDA

View Full Version : Poop excel gurus... could use a hand


pr_capone
04-19-2011, 04:37 PM
I need a formula that will return the date in which I will have over 1m.

bad cut, days are A and amounts are B

been trying to figure this out for 45 minutes now. I hate excel but I have to learn it.

http://i.imgur.com/D1yU9.png

DaFace
04-19-2011, 04:46 PM
If you switch the columns, you could do it with a vlookup.

=VLOOKUP(1000000,A6:B37,2,true)

would do it I think.

DaFace
04-19-2011, 04:49 PM
Is this for personal use or for a class or something? If you're looking for a formula that would do it without switching the columns, I don't know one right off hand, but could probably figure it out.

Donger
04-19-2011, 04:51 PM
It looks like you already figured it out in Row 42.

pr_capone
04-19-2011, 04:52 PM
If you switch the columns, you could do it with a vlookup.

=VLOOKUP(1000000,A6:B37,2,true)

would do it I think.

close... pulled up the day before

pr_capone
04-19-2011, 04:53 PM
Is this for personal use or for a class or something? If you're looking for a formula that would do it without switching the columns, I don't know one right off hand, but could probably figure it out.

nothing overly pressing... just for class.

pr_capone
04-19-2011, 04:56 PM
It looks like you already figured it out in Row 42.

Just placed it there to show what the needed result was

DaFace
04-19-2011, 04:56 PM
nothing overly pressing... just for class.

Hmm. Have you been using INDEX and MATCH formulas lately? You could probably do it that way, but it's a bit of a pain in the ass.

Donger
04-19-2011, 04:59 PM
Just placed it there to show what the needed result was

That's cheating.

DaFace
04-19-2011, 05:00 PM
=INDEX(A7:B37,MATCH(1000000,B7:B37),1)

That work?

pr_capone
04-19-2011, 05:05 PM
=INDEX(A7:B37,MATCH(1000000,B7:B37),1)

That work?

Nope, returned 30-jan

I havent been using the index feature in class but have briefly touched on vlookup so it is a safe bet to say the first thought was the right one.

reading my textbook tho...

use vlookup to search for exact matches or for the nearest value that is less than or equal to the search value.

which is why it returns the 27th. its looking for the lowest value under 1m.

DaFace
04-19-2011, 05:06 PM
Nope, returned 30-jan

I havent been using the index feature in class but have briefly touched on vlookup so it is a safe bet to say the first thought was the right one.

reading my textbook tho...

use vlookup to search for exact matches or for the nearest value that is less than or equal to the search value.

which is why it returns the 27th. its looking for the lowest value under 1m.

Could you just attach that sheet? It's kind of hard to play with these things without being able to just do it in Excel.

Dicky McElephant
04-19-2011, 05:06 PM
The real question is.....how exactly ARE you getting a million dollars in 28 days?

pr_capone
04-19-2011, 05:08 PM
Could you just attach that sheet? It's kind of hard to play with these things without being able to just do it in Excel.

you know what I did? I modified your first post to

=VLOOKUP(1500000,A6:B37,2,true)

which returned the correct answer. for the purposes of this specific assignment, that will do just fine.

thx for the assist Face!

DaFace
04-19-2011, 05:11 PM
you know what I did? I modified your first post to

=VLOOKUP(1500000,A6:B37,2,true)

which returned the correct answer. for the purposes of this specific assignment, that will do just fine.

thx for the assist Face!

Cool. In case anyone cares, I just tried my second one as well, and it seems to work fine too. INDEX and MATCH are pretty powerful, but they're a bit of a pain in the ass.

DaFace
04-19-2011, 05:12 PM
Oops. It was one off.

=INDEX(A7:B37,MATCH(1000000,B7:B37)+1,1)

would do it.