# Learning to use formulas to fit my needs...

#### Thausta

##### New Member
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.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
On 2002-09-08 20:30, Thausta wrote:
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.

Rob - welcome to the board!

Rather than recommending any of the many excel books / on-line tutorials , I'd suggest 'keep browsing' as one of the best ways of improving your understanding of how excel works. Of course, this will in part depend on what your current skills are like, how comfortable you are with 'computing' in general etc, but if you just stay here & have a look at the questions & answers you'll get to see a massive range of different problems coming up & the solutions being offered. Better, in my view, to learn by seeing excel in action (even if vicariously) than by reading through a book. That said, happy to suggest a few books if you want.

As far as the question goes, how about posting back with a bit more detail re what your data is, what outcomes you want etc & we can take it from there...

[No message]

The hang out, read, and learn method sounds good to me.
What I'm trying to do is, leave a cell where In cell B7, I type in the mortgage amount. Then another cell with a formula looks at B7 and says, ok, is it under \$10k? If so,
=B7*0.01.
Is it \$10k-\$500k? Then
=(B7*0.0075)-25
or, finally, is it over \$500k? If so,
=(B7*0.02125)-25
I tried putzing around with "IF" and ended up feeling like I should sing "IF I only had a brain."
There are other things I need this spreadsheet to do, but this is an important starter.
This is probably pretty basic stuff, but hey, you have to start somewhere, right? I need a crash course! lol
Thanks
Rob

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

[No message]

Will, thanks! I'll have disecting and the formula, and in the meanwhile it will be working for me.
Much appreciated.
Rob

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))

Here's another approach...

=B7*VLOOKUP(B7,{0,0.01;10000,0.0075;500000.01,0.02125},2)-IF(B7>=10000,25)

On 2002-09-09 10:36, Mark W. wrote:
Here's another approach...

=B7*VLOOKUP(B7,{0,0.01;10000,0.0075;500000.01,0.02125},2)-IF(B7>=10000,25)

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

Replies
1
Views
291
Replies
6
Views
500
Replies
2
Views
432
Replies
0
Views
505
Replies
2
Views
486

1,218,593
Messages
6,143,380
Members
450,484
Latest member
ChrisMac1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back