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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
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
511
Office Version
365
Platform
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
511
Office Version
365
Platform
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>
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,613
Messages
5,512,422
Members
408,893
Latest member
Abdulmemon

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top