thanks Pete. I was working on it and came up with anther formula that works.
i posted it to dave's idea. i'm gonna try yours too.

thanks everyone!

"Pete_UK" wrote:

Here's one way of doing it. If you have 20%, 18%, 16% etc in C1
downwards to C11 in -2% steps, then put the lower part of your ranges
in D1 downwards, i.e. 0, 50, 100, 200, 300, etc up to 900 in D11, then
put this formula in E1:

=INDEX(C$1:C$11,MATCH(A1,D$1:D$11,0))*B1

This will multiply B1 by the appropriate percentage depending on the
value in A1. Copy the formula down your 2000 rows.

Hope this helps.

Pete

On Apr 24, 3:41 pm,
wrote:
Each entry in C is related to the range. For example: for range 0-50, the C
value is 20%. for range 50-100, the C value is 18%. For now, the ranges all
go up at an even interval and the value in C goes down in an even interval.. I
can use just 7, but what are the other ways if I need more then 7 nests in
the future?

I have about 2000 lines of data that all have different values that will
fall into the 7 ranges. I only have (for now) 7 ranges and 7 values to use in
relation to the range. I'm hoping that I can write one formula that I can
apply down the last column for all 2000 lines of data.



"Pete_UK" wrote:
Yes, but how many ranges do you have? There is a limit of 7 nested
functions that you can have in Excel 2003 and earlier, so if you have
more ranges than 7 we can't use nested IFs - we have to do this in a
different way.

What are your ranges? Do they always go up in steps of 100, or does it
go something like 300-400, 400-500, then 500-750, 750-1000, then
1000-1500? What is the largest value that A1 can take? (And do you
have sufficient entries in column C?)

Pete

On Apr 24, 3:02 pm,
wrote:
Pete,
Sorry about that. That's a typo. The 2nd range is 50-100, the third range is
100-200, etc. I know excel doesn't understand "and so on", I was just trying
to illustrate that I have multiple ranges, not just the ones I listed.

Thanks

"Pete_UK" wrote:
What is the range of the second criteria? 50-100 or 50-200? How many
criteria ranges will you have, and what are they? (Excel doesn't
understand "... and so on ...").

Pete

On Apr 24, 2:24 pm,
wrote:
I'm trying to set a value that depends on another value being between two
values. For example: IF A1 is between 0-50, then A2 is B2*C2. If A1 is
greater then 50, but less then 100 but less then 200, then A2 is B2*C3.. If A1
is greater then 200 but less then 300, then A2is B2*C4. And so on.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -