Array formula

[HSalim[MVP]]

Hi,

I have a table of prices - a base price and a series of discounts on it as
price levels.
These are currently held as
ListPrice, Discount1, Discount2, ... , Price1 = ListPrice * Discount1,
Price2=...

Discounts 1 through 5 usually follow a few predictable patterns and I want
to replace the many discount columns with
one column containing an array of values
{10,11,12,14,16}

What formula would I use for Prices 1 through 5 that would give me
Listprice * Array(1), ListPrice * Array(2) ...

Thanks in advance
Habib

 

[Bernie Deitrick]

Habib,

I don't think that can be done - arrays can only be entered within array formulas as arrays, not as
cell references, IIRC. And the formulas to extract the values for use would be much more complex
than the simple =$A3*C3 style that you can use now.

HTH,
Bernie
MS Excel MVP

 

[HSalim[MVP]]

Bernie,
Thanks for the reply.
I was trying to see how I could solve the problem without having to write a
custom function...

I can achieve something really close using VLOOKUP

=VLOOKUP($B3,Scheme,$C$1+1)*$A3
or even
=VLOOKUP($B3,Scheme,Column()-2)*$A3"
I guess I should be happy with that. I still might try to parse an array
somehow.

Regards
HS

-----------Worksheet setup ---

First row and first column are excel row and column headings
_|a,b,c,d,e <-- Ignore this row
1|,,1,2,3
2|BasePrice,Scheme,Price1,Price2,Price3
3|1,D,13,23,33
4|1,B,11,21,31

Scheme (named Range)
A,10,20,30,40
B,11,21,31,41
C,12,22,32,42
D,13,23,33,43
E,14,24,34,44
F,15,25,35,45
G16,26,36,46
H,17,27,37,47

Range("C3").Formula = "=VLOOKUP($B3,Scheme,$C$1+1)*$A3"
Range("C4").Formula = "=VLOOKUP($B3,Scheme,$D$1+1)*$A3"
Range("C5").Formula = "=VLOOKUP($B3,Scheme,$E$1+1)*$A3"