SOLVED
Home

Excel Offset Function: Calculation Result Inconsistency Problem

%3CLINGO-SUB%20id%3D%22lingo-sub-873894%22%20slang%3D%22en-US%22%3EExcel%20Offset%20Function%3A%20Calculation%20Result%20Inconsistency%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873894%22%20slang%3D%22en-US%22%3E%3CP%3EI%20ran%20across%20this%20problem%20when%20I%20was%20doing%20%3Daverage(offset())%20calc.%20The%20two%20%22average(offset())%22%20functions%20generate%20different%20results.%20The%20only%20difference%20in%20the%20two%20functions%20are%20the%20optional%20parameter%20%5Bheight%5D%20-%20cell%20%22C16%22%20vs%20%E2%80%9CN16%E2%80%9D%2C%20where%20%22C16%22%20and%20%22N16%22%20both%20have%20numerical%20value%20of%208.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20really%20have%20no%20idea%20why%20these%202%20functions%20generate%20difference%20results.%20Anyone%20please%20help%3F%20Many%20thanks!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESpreadsheet%20attached.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20389px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133423i6E067D7C22C364F4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221.PNG%22%20title%3D%221.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-873894%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-874023%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Offset%20Function%3A%20Calculation%20Result%20Inconsistency%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-874023%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414784%22%20target%3D%22_blank%22%3E%40Divade%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThough%20this%20may%20not%20justify%20this%20behavior%2C%20the%20calculation%20in%20N16%20involves%20decimal%20numbers%20and%20that%20is%20influencing%20the%20height%20in%20the%20offset%20function%20in%20some%20way.%20The%20offset%20function%20which%20uses%20N16%20returns%20the%20range%20CT!%24F%243%3A%24F%249%20whereas%20the%20offset%20function%20which%20uses%20C16%20returns%20the%20range%20CT!%24F%243%3A%24F%2410%2C%20you%20will%20know%20this%20if%20you%20Evaluate%20both%20the%20formulas%20and%20the%20strange%20thing%20is%2C%20both%20C16%20and%20N16%20are%20evaluated%20as%208.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20use%20%3DAVERAGE(OFFSET(CT!%24F%243%2C0%2C0%2C%3CSTRONG%3EINT(N16)%3C%2FSTRONG%3E%2C1))%2F100%2C%20I%20get%20the%20same%20output%20as%20returned%20by%20the%20formula%20using%20C16.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInteresting%20thing%20is%2C%20the%20formula%20%3DC16%3DN16%20is%20evaluated%20as%20true%20so%20something%20strange%20is%20happening%20in%20the%20background%20and%20I%20have%20no%20idea%20about%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-874030%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Offset%20Function%3A%20Calculation%20Result%20Inconsistency%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-874030%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYeah%20that's%20the%20decimal%20that%20affects%20the%20height!%20Now%20I%20know%20at%20least%20how%20to%20improve%20the%20formula.%20Many%20thanks%20for%20your%20help%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-874217%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Offset%20Function%3A%20Calculation%20Result%20Inconsistency%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-874217%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414784%22%20target%3D%22_blank%22%3E%40Divade%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20you%20found%20it%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBtw%20if%20your%20calculation%20in%20N16%20allows%2C%20you%20may%20change%20the%20formula%20in%20N16%20itself%20to%20the%20following%20one%20and%20that%20will%20stop%20the%20inconsistent%20behavior%20of%20Offset%20function.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINT(WL_Num*C4*C10)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Divade
New Contributor

I ran across this problem when I was doing =average(offset()) calc. The two "average(offset())" functions generate different results. The only difference in the two functions are the optional parameter [height] - cell "C16" vs “N16”, where "C16" and "N16" both have numerical value of 8. 

 

I really have no idea why these 2 functions generate difference results. Anyone please help? Many thanks! 

 

Spreadsheet attached. 

 

 

1.PNG

3 Replies
Solution

@Divade 

Though this may not justify this behavior, the calculation in N16 involves decimal numbers and that is influencing the height in the offset function in some way. The offset function which uses N16 returns the range CT!$F$3:$F$9 whereas the offset function which uses C16 returns the range CT!$F$3:$F$10, you will know this if you Evaluate both the formulas and the strange thing is, both C16 and N16 are evaluated as 8.

 

If I use =AVERAGE(OFFSET(CT!$F$3,0,0,INT(N16),1))/100, I get the same output as returned by the formula using C16.

 

Interesting thing is, the formula =C16=N16 is evaluated as true so something strange is happening in the background and I have no idea about that.

@Subodh_Tiwari_sktneer 

Yeah that's the decimal that affects the height! Now I know at least how to improve the formula. Many thanks for your help  

@Divade 

You're welcome! Glad you found it helpful.

 

Btw if your calculation in N16 allows, you may change the formula in N16 itself to the following one and that will stop the inconsistent behavior of Offset function.

=INT(WL_Num*C4*C10)

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies