Multiple function question
Posted by Michael P on July 23, 2001 12:13 PM
I have created a range on numbers with the column headers as dates and numbers listed on rows below.
A B C
1 1/1 2/1 3/1
2 4.4 3.3 2.5
3 5.9 6.4 7.1
Can excel find a particular cell (=vlookup(A:1)), then take that cell and multiply the found cell with several cells to achieve a value( =product(A1:A3)) in one cell function?
Posted by Aladin Akyurek on July 23, 2001 12:22 PM
Michael,
Can you make your question a bit more specific, using the sample data? Apperantly, you want to retrieve the value in a particular cell and multiply that value with which ones?
Aladin
==============
Posted by Michael P on July 23, 2001 12:33 PM
I'll try...from another cell lookup date(a:1) which in this case is "1/1". Then locate the cell
directly below(A2) which is "4.4". Then take the
value in cell (A2) "4.4" and multiply (A2)"4.4" by the corresponding values to the right of the cell. In this case product(a2:C2) 4.4 x 3.3 x 2.5.
The cell should read 36.3.
Does this help?
Posted by Aladin Akyurek on July 23, 2001 1:14 PM
Michael,
Let me restate your question to see if I understand your problem.
You specify a date as lookup value: that gives us the column. Lets say that it is B1.
Then compute the product of all the values to the right of B2 including B2 (Right?). B2 is in row 2, right.
My question is "What is the criterion for picking up a particular row? For your example, you indicated for the date you want (1/1) the row of the value immediately below the date value. I'd like to know whether you have a way specifying the desired row.
Aladin
Posted by Michael P on July 23, 2001 1:54 PM
The ultimate goal is to create a dropdown box wheras I select a particular date(=vlookup(date),1,false). This formula would find the particular date then move one cell down from the date. This gives me my starting point for the
product calculation. The driver is the date.
From this point I want to create a product
array with the starting point. The range of this
product would be my starting point through the
ending point. Does this help?
Posted by Aladin Akyurek on July 23, 2001 2:46 PM
To be honest, not much. I'll try to express my worries by taking the following formula that computes the product you wanted.
=SUBTOTAL(6,INDIRECT(ADDRESS(ROW(A1:C1)+1,MATCH("01-Jan-01"+0,A1:C1,0))&":"&ADDRESS(ROW(A1:C1)+1,COUNT(A1:C1))))
Don't be concerned much about the way the specified date looks in the formula.
This formula tells you how to compute a product of a range. Thus:
=SUBTOTAL(6,D1:D10) will do the multiplication D1xD2xD3x...xD10.
This is on the positive side I guess.
If you have dropdown list where you select a date, you can use the cell ref of the list as the lookup value for the MATCH function (this can be done with VLOOKUP too, but there is no need for the complicated formula that using VLOOKUP would require).
This is on the positive side too.
As you probably see, I'm computing with the ADDRESS function the range that SUBTOTAL needs. The trouble starts in my opinion here. I don't understand, I guess, how you would want to proceed from the product just computed.
Aladin
Posted by Michael P on July 24, 2001 7:10 AM
The result is for a page which our marketing dept
can use. They input a date via drop down box. Excel links that date and calculates a return based on numbers in the database. I have completed the whole database table which took about a day and a half. For the next project I would like to eliminate data entry if possible.
Thanks for now I can at least experiment with the data provided.
