Product Function

rki1966

Active Member
Joined
Feb 1, 2004
Messages
351
{=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
Joined
May 28, 2005
Messages
54,259
Office Version
  1. 365
Platform
  1. Windows
A small set of sample data and expected result(s) should help clarify your question.
 

rki1966

Active Member
Joined
Feb 1, 2004
Messages
351
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
Joined
Aug 28, 2009
Messages
582
Office Version
  1. 365
Platform
  1. Windows
Hello

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

rki1966

Active Member
Joined
Feb 1, 2004
Messages
351

ADVERTISEMENT

great, thanks for your help
 

rki1966

Active Member
Joined
Feb 1, 2004
Messages
351
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
Joined
Aug 28, 2009
Messages
582
Office Version
  1. 365
Platform
  1. Windows
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>
 
Master Excel Bundle

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.

Forum statistics

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

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