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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
A small set of sample data and expected result(s) should help clarify your question.
 
Upvote 0
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%
 
Upvote 0
Hello

That would be:
Code:
{=PRODUCT(1+IF(A5:A27=C1,B5:B27))-1}
 
Upvote 0
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}
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top