Data is Same But Result is different in Excel Sheets

[LearnExcel&VBA]

I hope everyone is doing well, in good health.

I have two sheets attached below.

Problem is that Sheet 1 has data in Horizontal/Row wise which i have converted into Vertical in Sheet2 and i have verified the values that are same in both sheets which heading are Main, Month, 1, 2, and 3.

But there result is different in both sheets I have required this below picture Sheet1 same result into Sheet2 result i do not know why the difference is appearing when data is same.




Both sheet are attached below.

Any assistance you can provide would be greatly appreciated.

 

[p45cal]

To take one example formula:
Sheet1.xlsx, Sheet1, cell B10 reads:
=(PRODUCT(AO23,AP22:AZ23,BA22)-1)
I think it should read:
=(PRODUCT(AN23,AO22:AY23,AZ22)-1)
whereupon it agrees with cell V13 in Sheet2 of sheet2.xlsm.

The cells to the right of the above cells contain different formulae:
=(1+B10)^(1/12)-1
and
=(1+$V13)^(1/$X$3)-1
where the value in $X$3 is 7.4 so that formula resolves to:
=(1+$V13)^(1/7.4)-1

 

[LearnExcel&VBA]

p45cal
Sir, Actually issue is Sheet1 is fully accurate i have changed the sequence from Columns to Row wise in Sheet2. where that below picture sheet1 result is different from the sheet2.

 

[p45cal]

I'm surpised that sheet1 is accurate, because when I look at the ranges that that formula relates to it is:

Surely you want to include Jan 2023 data and not include Feb 2024 data?
If I'm wrong, then you need to adjust the formulae on Sheet2, but I doubt it.

 

[LearnExcel&VBA]

p45cal
sir if i follow the same formula then result is same but i have created the formula dynamically might which create the problem that is.

in Sheet2 Range J10
=PRODUCT(IF(($C$2:$C$1000<=$T10)*($C$2:$C$1000>$T9),($E$2:$E$1000)))*PRODUCT(IF(($C$2:$C$1000<$T10)*($C$2:$C$1000>=$T9),($F$2:$F$1000)))-1

 

[p45cal]

Confirm that Sheet1 is incorrect please; I looked at the formulae in Sheet2 and they seemed properly to refer to years without Feb of the second year:

 

[LearnExcel&VBA]

p45cal

Sir, sorry to interrupt that Sheet1 is accurate and is fulfilling the requirements. I just the changed it to Horizontal in Sheet2 and tried to make in Sheet2 Range J10 formula dynamically which creates the problem.

Sheet1 is accurate.

 

[p45cal]

Cell N13 in Sheet2, add a month to dates:

=PRODUCT(IF(($C$2:$C$1000<=EDATE($T13,1))*($C$2:$C$1000>EDATE($T12,1)),($E$2:$E$1000)))*PRODUCT(IF(($C$2:$C$1000<EDATE($T13,1))*($C$2:$C$1000>=EDATE($T12,1)),($F$2:$F$1000)))-1

but it nonsense, it is nonsense, it is nonsense.

 

[LearnExcel&VBA]

p45cal
Sir, as per your suggestion i manually copy the date from Sheet1 and paste in Sheet2 Col D, E, and F but result is still not same.

 

[p45cal]

p45cal
Sir, as per your suggestion i manually copy the date from Sheet1 and paste in Sheet2 Col D, E, and F but result is still not same.

I didn't make any such suggestion.

 

[LearnExcel&VBA]

p45cal

Thank you so much for the help. I have found the solution by striving again over again, that is i just changed the Sheet2 Col"T" Jan dates to Feb which giving the accurate result.

Thanks for the help.

1/1/2020​

1/1/2021​

1/1/2022​

1/1/2023​

1/1/2024​

1/1/2025​

1/1/2026​

1/1/2027​

1/1/2028​

1/1/2029​

1/1/2030​

1/1/2031​

 

[LearnExcel&VBA]

Cell N13 in Sheet2, add a month to dates:

=PRODUCT(IF(($C$2:$C$1000<=EDATE($T13,1))*($C$2:$C$1000>EDATE($T12,1)),($E$2:$E$1000)))*PRODUCT(IF(($C$2:$C$1000<EDATE($T13,1))*($C$2:$C$1000>=EDATE($T12,1)),($F$2:$F$1000)))-1

but it nonsense, it is nonsense, it is nonsense.

Sir, it is not. The problem was this.

The values you are thinking are wrong actually are the required result.

2/1/2020​

2/1/2021​

2/1/2022​

2/1/2023​

2/1/2024​

2/1/2025​

2/1/2026​

2/1/2027​

2/1/2028​

2/1/2029​

2/1/2030​

2/1/2031​

 

[p45cal]

As you like.

 

[LearnExcel&VBA]

p45cal
Sir, please do not mind and i apologies that i could not make my post understandable. Thanks.