Hi anyone feeling Bright!!! I ne 1

[DirectelTech]

Hi anyone feeling Bright!!!

I need to calculate a rental factor in excel.
I am given a rate sheet with break points, i.e 0 - 100.00, 100.01 - 1000.00 etc.
Depending on the selling price it will fit into one of these price breaks and then I an given a factor that I need to multiply the selling price by.

I can write a huge if statement to get this done but was wondering if there was a easier way of doing it.

Thanks in advance.

Kevin

 

[SkipVought]

Hi

All you need is 1. A lookup table with the FROM breakpoint value and the MATCH() function using the 1 Match Ref in the third argument. More later if needed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DirectelTech]

Oh ok sure Right!!!!
Lost me at the lookup table bit, then stumped me with the FROM bit

LOL

Sorry but i have never used those before, any chance of a sample.

Thanks Skip

 

[SkipVought]

From Thru
0. 100
100.01 1000

[tt]
=match(Rental,FromRange,1)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DirectelTech]

Hi Skip.

I came up with this formula:
=IF($N$2>=$O$11,SUM($N$2*(K3/1000)),IF(AND($N$2>$O$10,$N$2<$O$11),SUM($N$2*(J3/1000)),IF(AND($N$2>$O$9,$N$2<$O$10),SUM($N$2*(I3/1000)),IF(AND($N$2>$O$8,$N$2<$O$9),SUM($N$2*(H3/1000)),IF(AND($N$2>$O$7,$N$2<$O$8),SUM($N$2*(G3/1000)),IF(AND($N$2>$O$6,$N$2<$O$7),SUM($N$2*(F3/1000)),IF(AND($N$2>$O$5,$N$2<$O$6),SUM($N$2*(E3/1000)),IF(AND($N$2>$O$4,$N$2<$O$5),SUM($N$2*(D3/1000)),IF(AND($N$2>$O$3,$N$2<$O$4),SUM($N$2*(C3/1000)),0)))))))))

And believe it or not it works..

Thanks anyway, unless you can simplify this..

Cheers
Kevin

 

[xlbo]

your final formula should be
=N2*(vlookup(N2,factors,2,true)

in a seperate sheet,
in A1 enter 0. In B1 enter the factor for 0 - 100
in A2 enter 100.01. In B2 enter the factor for 100.01 - 1000
In A3 enter 1000.01. In B3 enter the factor for 1000.01 - whatever

continue until you have all your factor bandings listed

Name that range "factors"

If you are uncomfortable with naming ranges, simply reference the block of cells rather thna the named range e.g. =N2*(vlookup(N2,sheet2!$A$1:$B$10,2,true)

amend the $B$10 depending on the length of your list of factors and sheet2 as the name of your sheet where the lookup is held



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DirectelTech]

Wow, duh that is so easy, thanks Geoff. When I saw that you dont need an exact match in the Vlookup, all the blocks fell into place.

Thanks a mil

Regards

Kevin

 

[xlbo]

np - that was also what Skip was referring to with the match formula - again, with match you don't need an exact match. the "1" argument in match relates to "true" in my vlookup - you can use either or 1 = true and 0 - false for the purposes of excel

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question