# Product Function

#### rki1966

##### Active Member
{=PRODUCT(1+B5:B27)-1}

column A has the month and column B has the daily returns. How do I write a function that will work for each month based on column A. The function above is for month 1.

I do not want to select the rows, i want the function to understand what rows to calculate based on the month.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Peter_SSs

##### MrExcel MVP, Moderator
A small set of sample data and expected result(s) should help clarify your question.

#### rki1966

##### Active Member
If I put 1 in cell C1 then I get the results where column A = 1
If I put 2 in cell C1 then I get the results where column A = 2
If I put 3 in cell C1 then I get the results where column A = 3

Column A Column B
1 0.02%
1 0.01%
1 -0.01%
1 0.20%
1 2.10%
1 -1.20%
1 3.20%
1 -0.32%

2 1.90%
2 0.01%
2 -0.01%
2 -3.20%
2 -1.90%
2 0.34%
2 6.90%

3 0.20%
3 -5%
3 1.80%
3 0.75%
3 0.01%
3 -0.01%

#### shift-del

##### Well-known Member
Hello

That would be:
Code:
``{=PRODUCT(1+IF(A5:A27=C1,B5:B27))-1}``

#### rki1966

##### Active Member

great, thanks for your help

#### rki1966

##### Active Member
Is there a way use the offset function to write the formula for columns C, D, E ,F?

example:{=PRODUCT(1+IF(A5:A27=C1,B5:B27))-1} Here is column B.

I can use the following functions, but would like to use the offeset function instead.

{=PRODUCT(1+IF(A5:A27=D1,C5:C27))-1}
{=PRODUCT(1+IF(A5:A27=E1,D5:D27))-1}
{=PRODUCT(1+IF(A5:A27=F1,E5:E27))-1}

#### shift-del

##### Well-known Member
I would simply copy it to the right.

<table valign="middle" colspan="5" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="5" align="middle">Arbeitsblatt mit dem Namen 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">C</td><td align="middle">D</td><td align="middle">E</td><td align="middle">F</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td align="right">0</td><td align="right">0</td><td align="right">0</td><td align="right">0</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Zelle</td><td>Formel</td></tr><tr><td>C2</td><td>{=PRODUCT(1+IF(\$A\$5:\$A\$27=C1,B5:B27))-1}</td></tr><tr><td>D2</td><td>{=PRODUCT(1+IF(\$A\$5:\$A\$27=D1,C5:C27))-1}</td></tr><tr><td>E2</td><td>{=PRODUCT(1+IF(\$A\$5:\$A\$27=E1,D5:D27))-1}</td></tr><tr><td>F2</td><td>{=PRODUCT(1+IF(\$A\$5:\$A\$27=F1,E5:E27))-1}</td></tr></tbody></table><table style="font-family: Arial; font-size: 8pt; background-color: rgb(255, 255, 255);"><tbody><tr><td style="font-weight: bold;">Contains array formula!
</td></tr><tr><td>Do not enter the curly brackets {}.</td></tr><tr><td>Enter the formular with CTRL-SHIFT-ENTER instead of just ENTER.</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created and displayed with Tab2HTML (v2.4.1). ©Gerd alias Bamberg
</td></tr></tbody></table>

Replies
10
Views
713
Replies
1
Views
181
Replies
6
Views
355
Replies
3
Views
436
Replies
6
Views
564

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,493
Messages
5,832,015
Members
430,104
Latest member
briannnnleong

### 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?

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