• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Divide rows individually from two spreadsheets and put result on another sheet

W

westend9876

Member
Hi. I have closing prices for two currency pairs. One sheet is EURJPY! and the other is USDJPY! Each day there is a new row of closing prices. I need to divide price of one by price of another for each row by row and divide that number by the number of rows of data. This result will be placed in cell A1 on sheet Results!

Here is what I'm trying to do in cell A1 on Results!

SUM(EURJPY!B2/USDJPY!B2,EURJPY!B3/USDJPY!B3,EURJPY!B4/USDJPY!B4,EURJPY!B5/USDJPY!B5,EURJPY!B6/USDJPY!B6...)/6

The ... in the formula means that the data will continue to append as each day passes. Column B for each sheet EURJPY! and USDJPY! contains the price data.

I need a formula that can pull the data from each sheet row by row and append itself with the new daily data. I can not SUM column B on each sheet because in yields incorrect fractional results. It has to be row by row.

Thanks
 
Hi, westend9876!

If you defined 2 dynamic named ranges, OneList for 1st worksheet, and TwoList for 2nd one, as follows:
OneList: =DESREF(EURJPY!$A$2;;;CONTARA(EURJPY!$A:$A)-1;1) -----> in english: =OFFSET(EURJPY!$A$2,,,COUNTA(EURJPY!$A:$A)-1,1)
TwoList: =DESREF(USDJPY!$A$2;;;CONTARA(USDJPY!$A:$A)-1;1) -----> in english: =OFFSET(USDJPY!$A$2,,,COUNTA(USDJPY!$A:$A)-1,1)

Then you can use this array formula:
=PROMEDIO(OneList/TwoList) -----> in english: =AVERAGE(OneList/TwoList)
Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.

Just advise if any issue.

Regards!
 
NARAYANK991 said:
Hi ,

Your title says SUBTRACT but your formula is using DIVISION ; which is correct ?

Can you check this file ?

Narayan
Click to expand...
Hi. Sorry I meant divide and changed the title accordingly. Your result is correct. However, I do not follow the logic. What does _data mean in the formula you provided?

SUMPRODUCT(EURJPY_data/USDJPY_data)/ROWS(EURJPY_data)
 
Hi ,

These are named ranges ; you can click on Name Manager , and see their definition ; the range will expand to refer to fresh data as it is added.

Narayan
 
westend9876 said:
Hi. Sorry I meant divide and changed the title accordingly. Your result is correct. However, I do not follow the logic. What does _data mean in the formula you provided?

SUMPRODUCT(EURJPY_data/USDJPY_data)/ROWS(EURJPY_data)
Click to expand...
NARAYANK991 said:
Hi ,

These are named ranges ; you can click on Name Manager , and see their definition ; the range will expand to refer to fresh data as it is added.

Narayan
Click to expand...
Also, I inadvertently forgot a few steps. I also need the formula to derive at the result in cell A2 on the Results! sheet. I included Sheet1! that includes all of the raw data to derive at the result. However, I need the formula to calculate all the steps to come up with the result the same as you did for the result in cell A1.

Essentially, I will not be able to provide a Sheet1 with all of those raw calculations for each pair. The formula has to pull the various data directly from the named sheets EURJPY and USDJPY as these are just two sheets of 60.

Thanks.
 

Attachments

SirJB7 said:
Hi, westend9876!

If you defined 2 dynamic named ranges, OneList for 1st worksheet, and TwoList for 2nd one, as follows:
OneList: =DESREF(EURJPY!$A$2;;;CONTARA(EURJPY!$A:$A)-1;1) -----> in english: =OFFSET(EURJPY!$A$2,,,COUNTA(EURJPY!$A:$A)-1,1)
TwoList: =DESREF(USDJPY!$A$2;;;CONTARA(USDJPY!$A:$A)-1;1) -----> in english: =OFFSET(USDJPY!$A$2,,,COUNTA(USDJPY!$A:$A)-1,1)

Then you can use this array formula:
=PROMEDIO(OneList/TwoList) -----> in english: =AVERAGE(OneList/TwoList)
Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.

Just advise if any issue.

Regards!
Click to expand...
Also, I inadvertently forgot a few steps. I also need the formula to derive at the result in cell A2 on the Results! sheet. I included Sheet1! on the attached workbook that includes all of the raw data to derive at the result. However, I need the formula to calculate all the steps to come up with the result the same as you did for the result in cell A1.

Essentially, I will not be able to provide a Sheet1 with all of those raw calculations for each pair. The formula has to pull the various data directly from the named sheets EURJPY and USDJPY as these are just two sheets of 60.

Thanks.
 

Attachments

Hi, westend9876!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
PS: For the forgotten steps, just apply NARAYANK991's last formula to either any of both solutions.
PS2: I'm still wondering about this:
SirJB7 said:
Just curious, what would that figure represent?
Click to expand...
 
SirJB7 said:
Hi, westend9876!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
PS: For the forgotten steps, just apply NARAYANK991's last formula to either any of both solutions.
PS2: I'm still wondering about this:
Click to expand...
The figures represent the mean of the data sets and the respective squared mean.
 
westend9876 said:
Hi. Sorry I meant divide and changed the title accordingly. Your result is correct. However, I do not follow the logic. What does _data mean in the formula you provided?

SUMPRODUCT(EURJPY_data/USDJPY_data)/ROWS(EURJPY_data)
Click to expand...
How would I make an Indirect reference to the letters EURJPY and USDJPY? For example here is my attempt but it does not work:

C2................D2
EURJPY........ USDJPY

SUMPRODUCT(INDIRECT(C2)&"_data")/INDIRECT(D2)&"_data")/ROWS(INDIRECT(C2)&"_data")
 
Somendra Misra said:
Can you Post your file?
Click to expand...
Hi I cannot post the file at this moment. But the named ranges are EURJPY_data and USDJPY_data. This formula below yields the correct results when I specify the full named range in the formula:

SUMPRODUCT(EURJPY_data/USDJPY_data)/ROWS(EURJPY_data)

However, I need to refer to the prefix of the named ranges in another cell. Hence, my attempt to use the Indirect function. So, the letters EURJPY are in cell C2 and USDJPY are in D2.

Thank you
 
@westend9876

Your Named range also contains refrence to EURJPY & USDJPY. I thing thats why it is not working.
If you define EURJPY_DATA as :--> ="!$A$2:$A$175" (Similarly for USDJPY_DATA) & Than use formula like.

SUMPRODUCT(INDIRECT(C2&EUR_DATA)/Indirect(D2&USDJPY_data)/ROWS(INDIRECT(C2&EUR_DATA)))

Just try this.

Regards,
 
Somendra Misra said:
@westend9876

Your Named range also contains refrence to EURJPY & USDJPY. I thing thats why it is not working.
If you define EURJPY_DATA as :--> ="!$A$2:$A$175" (Similarly for USDJPY_DATA) & Than use formula like.

SUMPRODUCT(INDIRECT(C2&EUR_DATA)/Indirect(D2&USDJPY_data)/ROWS(INDIRECT(C2&EUR_DATA)))

Just try this.

Regards,
Click to expand...
No that one did not work either. Thank you though.
 
Hi ,

This is a known incompatibility between the INDIRECT function and dynamic named ranges.

EURJPY_data has been defined as a dynamic named range , using the definition :

=EURJPY!$B$2:INDEX(EURJPY!$B:$B,COUNTA(EURJPY!$B:$B))

However , since the data extends till row 175 , if you change the above definition to a static one as follows :

=EURJPY!$B$2:$B$175

then you can use the INDIRECT function. However , even in this situation , the usage should be :

=SUMPRODUCT(INDIRECT(C2)/INDIRECT(D2))/ROWS(INDIRECT(C2))

where C2 contains : EURJPY_data

and D2 contains : USDJPY_data

Narayan
 
NARAYANK991 said:
Hi ,

This is a known incompatibility between the INDIRECT function and dynamic named ranges.

EURJPY_data has been defined as a dynamic named range , using the definition :

=EURJPY!$B$2:INDEX(EURJPY!$B:$B,COUNTA(EURJPY!$B:$B))

However , since the data extends till row 175 , if you change the above definition to a static one as follows :

=EURJPY!$B$2:$B$175

then you can use the INDIRECT function. However , even in this situation , the usage should be :

=SUMPRODUCT(INDIRECT(C2)/INDIRECT(D2))/ROWS(INDIRECT(C2))

where C2 contains : EURJPY_data

and D2 contains : USDJPY_data

Narayan
Click to expand...
I need to keep the range dynamic though as the data appends daily. I will have to find another solution then since there is an incompatibility.

Just an FYI, I did change the definition of EURJPY_data and USDJPY_data to static as you described above, changed C2 and D2 as instructed, and entered the new SUMPRODUCT formula with Indirect function as instructed. However, that did not work either.

Thank you for your assistance.
 
You must log in or register to reply here.
Back
Top