Learning to use formulas to fit my needs...

Thausta

New Member
Joined
Sep 7, 2002
Messages
21
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
On 2002-09-08 20:30, Thausta wrote:
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.

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

Paddy
 

Thausta

New Member
Joined
Sep 7, 2002
Messages
21
Paddy,
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
 

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143

ADVERTISEMENT

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
 

Thausta

New Member
Joined
Sep 7, 2002
Messages
21

ADVERTISEMENT

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

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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))
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Here's another approach...

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

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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
 

Forum statistics

Threads
1,144,213
Messages
5,723,055
Members
422,476
Latest member
beck85

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top