Range Chart with IF result

[QAGuy]

I was wondering if someone could point in the right direction in order to come up with a function that might work for looking at a numerical range and coming back with a result.

Example

From 0-52 weeks = 1.5
53-104 weeks = 1.7

So if a cell eqauls any number from 53-104 it would give a result of 1.7 or if a cell had 0-52 it would give a result of 1.5 so on and so forth.

[Pete_UK]

Do the numbers go above 104, and if so is there a similar increase of 0.2 on the previous year's week numbers? What column is your week numbers in?

Pete

[QAGuy]

Yes the numbers go above 104 and no they don't increase incremetally by .2. The cells would look like this

A1 A2 A3
0 52 1.5
53 104 1.7
ETC

Example if A5=75 then A6=1.7 according to the ranges above.

[Pete_UK]

I meant what would the value returned be if the numbers were in the range 105 to 156, or 157 to 208, and so on.

Pete

[QAGuy]

0 52 1.53846
53 104 1.69231
105 156 1.84615
157 208 2
209 260 2.15384
261 312 2.30769
313 364 2.46153
365 416 2.61538
417 468 2.76923
469 520 2.92307
521> 3.07692

[Pete_UK]

Okay, you can just put that 3-column table somewhere in your sheet (suppose it occupies columns L, M and N starting in row 1). Then, if your week number is in cell A5, as you suggested in Post #3, then you can use this formula in A6 (or B5 if you want to copy it down alongside other week number values):

=VLOOKUP(A5,$L:$N,3)

and it will return the appropriate value for the number of weeks.

Hope this helps.

Pete

[QAGuy]

Thanks I'll give it a try, I originally thought about using VLOOKUP.

[Pete_UK]

Strictly speaking, you don't need the middle column of that table, but it might make more sense when reading it if it is left in.

Pete

[QAGuy]

Seems to have worked thanks for your help

[Pete_UK]

You're welcome - glad to be of help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete