Prev: Show formula result in another sheet
Next: Conditional format question
From: Jason on 15 Dec 2009 17:28
I have a possibility of entering a dollar value in C10 - C14. Once a number
is used in C10 it will not be used again in the next months D10, E10 and so
on. The same for C11-14. there might not be a number entered in those sells
for months to come. IT might be G11 or later on. I set up another cell with
the following formula:

=IF(C10=0,0,(LOOKUP(C10,Sheet1!$C$7:$C$12,Sheet1!$E$7:$E$12)))

This references table that looks like this:
Period 1 Periood 2 Period 3 Period 4
Level 1A 1 to 50 $250 0.01% 12.50% 12.50% 20.00%
Level 2A 51 to 150 $12,500 0.01% 8.33% 8.33% 8.33%
Level 3A 151 to 500 $37,500 0.01% 6.25% 6.25% 6.25%
Level 4A 501 to 750 $125,000 0.01% 5.00% 5.00% 5.00%
Level 5A 750 to 1000 $187,500 0.01% 4.17% 4.17% 4.17%
Level 6A 1001+ $250,000 0.01% 3.57% 3.57% 3.57%

In this case the result of the formula is: 12.5%

This is great, however, I need to formula to take into consideration in the
next cell that instead of period 1, I need the result to be period 2 and so
on.

I hope this is clear what I am asking, if not let me know. Any help would
be greatly appreciated.

Thanks!
From: Paul C on 16 Dec 2009 10:33
Using Vlookup will allow you to specify a column
=IF(C10=0,0,VLOOKUP(C10,Sheet1!$C$7:$G$12,3,true))
would look up results in Column E (the third column of your array)

You can replace the 3 with some formula that evaluates to your desired
period (Column 1 is you lookup value so Period 1 would be column 2, Period 2
- Column3, etc..)

Since you did not give an example of the are using these formulas, I cannot
specifiy exactly how to vary the Vlookup column to give you your desired
period

Hope this get you started in the right direction
--
If this helps, please remember to click yes.


"Jason" wrote:

> I have a possibility of entering a dollar value in C10 - C14. Once a number
> is used in C10 it will not be used again in the next months D10, E10 and so
> on. The same for C11-14. there might not be a number entered in those sells
> for months to come. IT might be G11 or later on. I set up another cell with
> the following formula:
>
> =IF(C10=0,0,(LOOKUP(C10,Sheet1!$C$7:$C$12,Sheet1!$E$7:$E$12)))
>
> This references table that looks like this:
> Period 1 Periood 2 Period 3 Period 4
> Level 1A 1 to 50 $250 0.01% 12.50% 12.50% 20.00%
> Level 2A 51 to 150 $12,500 0.01% 8.33% 8.33% 8.33%
> Level 3A 151 to 500 $37,500 0.01% 6.25% 6.25% 6.25%
> Level 4A 501 to 750 $125,000 0.01% 5.00% 5.00% 5.00%
> Level 5A 750 to 1000 $187,500 0.01% 4.17% 4.17% 4.17%
> Level 6A 1001+ $250,000 0.01% 3.57% 3.57% 3.57%
>
> In this case the result of the formula is: 12.5%
>
> This is great, however, I need to formula to take into consideration in the
> next cell that instead of period 1, I need the result to be period 2 and so
> on.
>
> I hope this is clear what I am asking, if not let me know. Any help would
> be greatly appreciated.
>
> Thanks!
 | 
Pages: 1
Prev: Show formula result in another sheet
Next: Conditional format question