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

--
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 40
--------------------------------------------
Share your knowledge. Add your favorite questions and answers
Help add questions to this site! We want Your input.
--------------------------------------------

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]" wrote in message ...
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

--
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 40
--------------------------------------------
Share your knowledge. Add your favorite questions and answers
Help add questions to this site! We want Your input.
--------------------------------------------

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"