How to multiple different ranges of cells in an automated way?

[douglascaixeta]

Hi,

I have an excel sheet as showed below, and I would like to multiple the number of column 2, but the range is different all the time.

JAN 11
FEV 63
MAR 87
ABR 89
MAI 56
JUN 4
JUL 78
AGO 56
SET 45
OUT 22
NOV 1,2
DEZ 1,4

For example:
One event occur on Jan and finish on Mar. So, I inform the excel this 2 months and he should multiple 11, 63 and 87.

This event could be whatever, staring on Abr and finishing on Set. But never crossing, like starting on Nov and finishing on Jan. Because in the real database there is also the year variable, but is not important here. So I a bit easier. But how can a do it?

I think on using VLOOKUP. Then I have the first value and the last one. But how can I tell excel to multiple the range?
The ideal will be a VLOOKUP that returns the ADDRESS of the cell, not the value. But I didn't see this possibility.

Thanks in advance.

 

[gecs]

The PRODUCT function used with a range as argument (PRODUCT(A1:A12)) will multiply only the numbers found in the range - text values or empty cells will be ignored, so if it's possible to approximate the maximum length of the range you want to calculate, you may place the formula in a cell on, let's say, row 101 and multiply all the 100 numbers above (they might be 100, or 50 or only 3, the formula will return an accurate result).

 

[gardnertoo]

I wonder if "multiple the number of column 2" should be interpreted as a SUM? If so, maybe this is what you're after:

Book2
	A	B	C	D	E
1	JAN	11		Start:	JAN
2	FEV	63		End:	MAR
3	MAR	87		Multiple:	161
4	ABR	89
5	MAI	56
6	JUN	4
7	JUL	78
8	AGO	56
9	SET	45
10	OUT	22
11	NOV	12
12	DEZ	14
Sheet1

The formula in cell E3 looks for the month names in cells E1 and E2, then adds together the numbers for every month inside that range of months. Here's that formula:

=SUM(OFFSET(B1,MATCH(E1,A1:A12,FALSE)-1,0,MATCH(E2,A1:A12,FALSE)-MATCH(E1,A1:A12,FALSE)+1,1))

Hope this helps. If not, reply back and we will try to get it right for you.

 

[douglascaixeta]

Hi gecs,

I don't understand your solution. The range of the cells will change. The real database is huge. I can't select with PRODUCT function all the time. Is a lot of products and they have different ranges. If there is a VLOOKUP for ADDRESS I could use the result in a PRODUCT function.

I can also inform the first month and the number of months ahead. For example ABR and 4. So excel will multiple ABR, MAI, JUN and JUL.

 

[gardnertoo]

If the search is ABR, MAI, JUN and JUL, those four numbers in your example data set are 89, 56, 4, and 78. What should the answer be:
A) 89 x 56 x 4 x 78 = 1,555,008
B) 89 + 56 + 4 + 78 = 227

 

[gecs]

Ok, I understood you want to multiply the values in a range of a variable length. Now, also reading gardnertoo's post , I realise his intuition is correct ("multiple" should be interpreted as SUM). Following his solution will lead you to the desired result.

 

[douglascaixeta]

[SOLVED] Re: How to multiple different ranges of cells in an automated way?

Thank you very much gardnertoo! It works perfectly.

I took some time to do the checks and it works.

I will put the file here, so somebody can use the idea as well.

http://rapidshare.com/files/211926098/IPCA_Inflation_Brazil.xls.html

The idea is to calculate the return of the investments (ROI) discounting the inflation. So I put 2 parameters, Year and Month of start and end of the investment. The formulation will search for the numbers on the database and multiply (PRODUCT) all the values and return the percentage.

The worksheet is just an example and is with inflation rates of Brazil, which is useful for me, and I will connect with other sheets, where I control the investments.

Thank you very much.

 

[douglascaixeta]

I have another problem now, with a similar database. I would like to do the same thing, multiple a range of values, giving 2 parameters, the date.
For example: here is the table (only 2 months)

****** http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8"> <title></title> ****** name="GENERATOR" content="BrOffice.org 3.0 (Linux)"> <style> <!-- BODY,DIV,TABLE,THEAD,TBODY,TFOOT,TR,TH,TD,P { font-family:"Arial"; font-size:x-small } --> </style> <table border="0" cellspacing="0" cols="2" rules="none" frame="void"> <colgroup><col width="86"><col width="86"></colgroup> <tbody> <tr> <td align="left" width="86" height="16">Data</td> <td align="left" width="86">Value</td> </tr> <tr> <td sdval="38718" sdnum="1046;0;DD/MM/AA" align="right" height="16">01/01/06</td> <td sdval="0,7338" sdnum="1046;" align="right">0,73</td> </tr> <tr> <td sdval="38719" sdnum="1046;0;DD/MM/AA" align="right" height="16">02/01/06</td> <td sdval="0,7626" sdnum="1046;" align="right">0,76</td> </tr> <tr> <td sdval="38720" sdnum="1046;0;DD/MM/AA" align="right" height="16">03/01/06</td> <td sdval="0,7577" sdnum="1046;" align="right">0,76</td> </tr> <tr> <td sdval="38721" sdnum="1046;0;DD/MM/AA" align="right" height="16">04/01/06</td> <td sdval="0,7609" sdnum="1046;" align="right">0,76</td> </tr> <tr> <td sdval="38722" sdnum="1046;0;DD/MM/AA" align="right" height="16">05/01/06</td> <td sdval="0,7256" sdnum="1046;" align="right">0,73</td> </tr> <tr> <td sdval="38723" sdnum="1046;0;DD/MM/AA" align="right" height="16">06/01/06</td> <td sdval="0,6919" sdnum="1046;" align="right">0,69</td> </tr> <tr> <td sdval="38724" sdnum="1046;0;DD/MM/AA" align="right" height="16">07/01/06</td> <td sdval="0,6916" sdnum="1046;" align="right">0,69</td> </tr> <tr> <td sdval="38725" sdnum="1046;0;DD/MM/AA" align="right" height="16">08/01/06</td> <td sdval="0,7252" sdnum="1046;" align="right">0,73</td> </tr> <tr> <td sdval="38726" sdnum="1046;0;DD/MM/AA" align="right" height="16">09/01/06</td> <td sdval="0,7585" sdnum="1046;" align="right">0,76</td> </tr> <tr> <td sdval="38727" sdnum="1046;0;DD/MM/AA" align="right" height="16">10/01/06</td> <td sdval="0,7545" sdnum="1046;" align="right">0,75</td> </tr> <tr> <td sdval="38728" sdnum="1046;0;DD/MM/AA" align="right" height="16">11/01/06</td> <td sdval="0,7382" sdnum="1046;" align="right">0,74</td> </tr> <tr> <td sdval="38729" sdnum="1046;0;DD/MM/AA" align="right" height="16">12/01/06</td> <td sdval="0,7191" sdnum="1046;" align="right">0,72</td> </tr> <tr> <td sdval="38730" sdnum="1046;0;DD/MM/AA" align="right" height="16">13/01/06</td> <td sdval="0,6721" sdnum="1046;" align="right">0,67</td> </tr> <tr> <td sdval="38731" sdnum="1046;0;DD/MM/AA" align="right" height="16">14/01/06</td> <td sdval="0,6799" sdnum="1046;" align="right">0,68</td> </tr> <tr> <td sdval="38732" sdnum="1046;0;DD/MM/AA" align="right" height="16">15/01/06</td> <td sdval="0,7124" sdnum="1046;" align="right">0,71</td> </tr> <tr> <td sdval="38733" sdnum="1046;0;DD/MM/AA" align="right" height="16">16/01/06</td> <td sdval="0,7536" sdnum="1046;" align="right">0,75</td> </tr> <tr> <td sdval="38734" sdnum="1046;0;DD/MM/AA" align="right" height="16">17/01/06</td> <td sdval="0,7455" sdnum="1046;" align="right">0,75</td> </tr> <tr> <td sdval="38735" sdnum="1046;0;DD/MM/AA" align="right" height="16">18/01/06</td> <td sdval="0,7407" sdnum="1046;" align="right">0,74</td> </tr> <tr> <td sdval="38736" sdnum="1046;0;DD/MM/AA" align="right" height="16">19/01/06</td> <td sdval="0,7098" sdnum="1046;" align="right">0,71</td> </tr> <tr> <td sdval="38737" sdnum="1046;0;DD/MM/AA" align="right" height="16">20/01/06</td> <td sdval="0,679" sdnum="1046;" align="right">0,68</td> </tr> <tr> <td sdval="38738" sdnum="1046;0;DD/MM/AA" align="right" height="16">21/01/06</td> <td sdval="0,6684" sdnum="1046;" align="right">0,67</td> </tr> <tr> <td sdval="38739" sdnum="1046;0;DD/MM/AA" align="right" height="16">22/01/06</td> <td sdval="0,7" sdnum="1046;" align="right">0,7</td> </tr> <tr> <td sdval="38740" sdnum="1046;0;DD/MM/AA" align="right" height="16">23/01/06</td> <td sdval="0,73" sdnum="1046;" align="right">0,73</td> </tr> <tr> <td sdval="38741" sdnum="1046;0;DD/MM/AA" align="right" height="16">24/01/06</td> <td sdval="0,7471" sdnum="1046;" align="right">0,75</td> </tr> <tr> <td sdval="38742" sdnum="1046;0;DD/MM/AA" align="right" height="16">25/01/06</td> <td sdval="0,7346" sdnum="1046;" align="right">0,73</td> </tr> <tr> <td sdval="38743" sdnum="1046;0;DD/MM/AA" align="right" height="16">26/01/06</td> <td sdval="0,6938" sdnum="1046;" align="right">0,69</td> </tr> <tr> <td sdval="38744" sdnum="1046;0;DD/MM/AA" align="right" height="16">27/01/06</td> <td sdval="0,677" sdnum="1046;" align="right">0,68</td> </tr> <tr> <td sdval="38745" sdnum="1046;0;DD/MM/AA" align="right" height="16">28/01/06</td> <td sdval="0,6389" sdnum="1046;" align="right">0,64</td> </tr> <tr> <td sdval="38749" sdnum="1046;0;DD/MM/AA" align="right" height="16">01/02/06</td> <td sdval="0,5729" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38750" sdnum="1046;0;DD/MM/AA" align="right" height="16">02/02/06</td> <td sdval="0,5737" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38751" sdnum="1046;0;DD/MM/AA" align="right" height="16">03/02/06</td> <td sdval="0,5793" sdnum="1046;" align="right">0,58</td> </tr> <tr> <td sdval="38752" sdnum="1046;0;DD/MM/AA" align="right" height="16">04/02/06</td> <td sdval="0,5773" sdnum="1046;" align="right">0,58</td> </tr> <tr> <td sdval="38753" sdnum="1046;0;DD/MM/AA" align="right" height="16">05/02/06</td> <td sdval="0,5773" sdnum="1046;" align="right">0,58</td> </tr> <tr> <td sdval="38754" sdnum="1046;0;DD/MM/AA" align="right" height="16">06/02/06</td> <td sdval="0,5753" sdnum="1046;" align="right">0,58</td> </tr> <tr> <td sdval="38755" sdnum="1046;0;DD/MM/AA" align="right" height="16">07/02/06</td> <td sdval="0,572" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38756" sdnum="1046;0;DD/MM/AA" align="right" height="16">08/02/06</td> <td sdval="0,5771" sdnum="1046;" align="right">0,58</td> </tr> <tr> <td sdval="38757" sdnum="1046;0;DD/MM/AA" align="right" height="16">09/02/06</td> <td sdval="0,5708" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38758" sdnum="1046;0;DD/MM/AA" align="right" height="16">10/02/06</td> <td sdval="0,5613" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38759" sdnum="1046;0;DD/MM/AA" align="right" height="16">11/02/06</td> <td sdval="0,5588" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38760" sdnum="1046;0;DD/MM/AA" align="right" height="16">12/02/06</td> <td sdval="0,5588" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38761" sdnum="1046;0;DD/MM/AA" align="right" height="16">13/02/06</td> <td sdval="0,5662" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38762" sdnum="1046;0;DD/MM/AA" align="right" height="16">14/02/06</td> <td sdval="0,5538" sdnum="1046;" align="right">0,55</td> </tr> <tr> <td sdval="38763" sdnum="1046;0;DD/MM/AA" align="right" height="16">15/02/06</td> <td sdval="0,5643" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38764" sdnum="1046;0;DD/MM/AA" align="right" height="16">16/02/06</td> <td sdval="0,5616" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38765" sdnum="1046;0;DD/MM/AA" align="right" height="16">17/02/06</td> <td sdval="0,5679" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38766" sdnum="1046;0;DD/MM/AA" align="right" height="16">18/02/06</td> <td sdval="0,5695" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38767" sdnum="1046;0;DD/MM/AA" align="right" height="16">19/02/06</td> <td sdval="0,5695" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38768" sdnum="1046;0;DD/MM/AA" align="right" height="16">20/02/06</td> <td sdval="0,5713" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38769" sdnum="1046;0;DD/MM/AA" align="right" height="16">21/02/06</td> <td sdval="0,5677" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38770" sdnum="1046;0;DD/MM/AA" align="right" height="16">22/02/06</td> <td sdval="0,5514" sdnum="1046;" align="right">0,55</td> </tr> <tr> <td sdval="38771" sdnum="1046;0;DD/MM/AA" align="right" height="16">23/02/06</td> <td sdval="0,5585" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38772" sdnum="1046;0;DD/MM/AA" align="right" height="16">24/02/06</td> <td sdval="0,5619" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38773" sdnum="1046;0;DD/MM/AA" align="right" height="16">25/02/06</td> <td sdval="0,5571" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38774" sdnum="1046;0;DD/MM/AA" align="right" height="16">26/02/06</td> <td sdval="0,5571" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38775" sdnum="1046;0;DD/MM/AA" align="right" height="16">27/02/06</td> <td sdval="0,5571" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38776" sdnum="1046;0;DD/MM/AA" align="right" height="16">28/02/06</td> <td sdval="0,5865" sdnum="1046;" align="right">0,59</td> </tr> </tbody> </table>
For each month, there is 28 days and 28 respective values.

I would give 2 dates: 03/01/06 and the end month 02 (Fev), because the day will always be the same of the first date. The year can change.

With those 2 parameters the excel should get the value from 03/01/06 (0,76) and multiple with the value same day and the month that I informed (0,58).

With 2 months is easy, but I have thousands of months. 2006 until 2009.

So, is the same case of my first post, but now, instead of using all the values of the range, I have to use only the value of each month in the range.