Learning to use formulas to fit my needs...

[Thausta]

I'm a beginner looking to learn more about using formulas. Can anyone point me to a link online?
I'm developing a worksheet for my title closing activities. I need a formula that will look at one cell, determine which of three number ranges the entry falls into, and then apply a specific calculation to that entry based on the determined category.
If anyone can give me a boost on how to get THAT going in the meanwhile, I'd greatly appreciate it. It's kind of important/urgent, and I'd like to get it up and running before I even fully understand it yet, if possible LOL. I'll catch up on the knowledge as I go along.
Thanks.
Rob W.

 

[Mark W.]

...and this one ?
=B7*VLOOKUP(B7,{0,0.01;10000,0.0075;500000.01,0.02125},2)-(B7>=10000)*25

If you don't mind adding another arithmetic operation (i.e., ...*25).

 

[Thausta]

I really appreciate all the help. And since you guys seem as "into" this as I am into swing dancing, can I up the ante?

How about this: for the mortgage value ranges mentioned in my previous post (*0.01, etc.), there are actually two separate sets of rates to be applied, depending on where you are.

The one I asked about in my post reflects the rates for Long Island. NYC has it's own set of rates, and I thought maybe I could handle it this way, if only someone savvy enough can tell me how!:

I'd like to reserve cell C2 to type in "1" for the Island and "2" if it's in the city, and have the formula apply the applicable rates based on that.

So, using Juan's latest suggestion for an example, if I type in 1, the formula would calculate using the rates you already saw:
=B7*VLOOKUP(B7,{0,0.01;10000,0.0075;500000.01,0.02125},2)-(B7>=10000)*25
But if I type in 2, then the formula would use the city rates:
=B7*VLOOKUP(B7,{0,0.02;10000,0.0175;500000.01,0.02125},2)-(B7>=10000)*25
How can I accomplish THAT, if possible (which I'm sure for you Exelologists, it is!)

Thanks, again, greatly appreciative of the assistance!
Rob

 

[PaddyD]

Rob,

Does this help?

Book6
	A	B	C	D
1	Band	1	2
2	0	0.01	0.02
3	10000	0.0075	0.0175
4	500000.01	0.02125	0.2125
5
6
7
8	WhichBand?	1	2
9	WhatNumber?	225000	225000
10	Whatresult?	1662.5	3912.5
11
Sheet3

Things to note:

1) I've changed Juan's 'hard coded' lookup table to an actual table on the spreadsheet
2) check out vloouk in the help file. One of the arguments is 'column number' which is the column number of the lookup table you want the value returned from. Our lookup table has 3 columns. Because, e.g. the values for band '1' are in the second column, we can use 'bandvalue + 1' to point to the right column.
3) the absolute references (cell references with $ signs in front of them) don't change when you copy formulas from one place to another. Again, check them out in the help file if you need.

Does this give you the answers you expect?

Paddy
This message was edited by PaddyD on 2002-09-09 20:41

 

[WillR]

On 2002-09-09 10:30, Juan Pablo G. wrote:

On 2002-09-09 01:49, WillR wrote:
Here's an IF statement that will do the calc you mentioned.

=IF(B7<10000,B7*0.01,IF(AND(B7>=10000,B7<500000),(B7*0.0075)-25,IF(B7>=500000,(B7*0.02125)-25)))

Will

Will, just to keep "improving", you have some redundant checks in there.

For example, your first IF checks to see if B7 is less than 10,000, then if you get to the "Else" part, B7 is NOT less than 10000, therefor, must be greater or equal to 10000, right ? and the same happens with the last IF, you already checked if B7 was less than 500000, so, checking to see if its greater or equal than it is not necesary.

That said, your formula can be shortened to

=IF(B7<10000,B7*0.01,IF(B7<500000,B7*0.0075-25,B7*0.02125-25))

JPG.

Thanks for that....I know i've been (and still am) on a STEEEP learning curve since starting to try & improve both my excel & access skills via these boards. Thanks for your (&everyone's) help & support.

Regards,

 

[Thausta]

Paddy,
Going to try your suggestion. May take me a day to actually get to it due to insane schedule. I'll let you know how it goes.
Thanks!
Rob

 

[Thausta]

Paddy,
Excel-lent! That table/formula did exactly what I was looking for. Thanks!
Of course now I've encountered another road block, but I'll try to find the answer out there before posting it as a new topic.
Thanks to all...
Rob