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

douglascaixeta

New Member
Joined
Mar 21, 2009
Messages
38
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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

gecs

Active Member
Joined
Jan 26, 2009
Messages
320
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

Well-known Member
Joined
Jul 24, 2007
Messages
938
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
ABCDE
1JAN11Start:JAN
2FEV63End:MAR
3MAR87Multiple:161
4ABR89
5MAI56
6JUN4
7JUL78
8AGO56
9SET45
10OUT22
11NOV12
12DEZ14
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

New Member
Joined
Mar 21, 2009
Messages
38
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.
 
Last edited:

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938

ADVERTISEMENT

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

Active Member
Joined
Jan 26, 2009
Messages
320
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

New Member
Joined
Mar 21, 2009
Messages
38
[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

New Member
Joined
Mar 21, 2009
Messages
38
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,393
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top