SOLVED

Please Help With this Excel Problem!

%3CLINGO-SUB%20id%3D%22lingo-sub-2597023%22%20slang%3D%22en-US%22%3EPlease%20Help%20With%20this%20Excel%20Problem!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2597023%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20solve%20this%20last%20part%20to%20the%20problem%20and%20cannot%20seem%20to%20find%20a%20correct%20answer.%20I%20have%20attached%20both%20screenshots%20to%20show%20what%20I%20am%20dealing%20with.%20I%20would%20love%20an%20answer%20that%20includes%20the%20formula!%20Thank%20you!%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Help%201.PNG%22%20style%3D%22width%3A%20711px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F299703iD468ED0300EE63EE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Help%201.PNG%22%20alt%3D%22Help%201.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Help%202.PNG%22%20style%3D%22width%3A%20658px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F299705i007434139D31DBAF%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Help%202.PNG%22%20alt%3D%22Help%202.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2597023%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2597580%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%20With%20this%20Excel%20Problem!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2597580%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1117369%22%20target%3D%22_blank%22%3E%40lanem1010%3C%2FA%3E%26nbsp%3BSince%20your%20assignment%20explicitly%20requires%20the%20use%20of%20OFFSET%2C%20I%20guess%20you%20are%20not%20really%20interested%20in%20solutions%20that%20do%20not%20include%20this%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20left%20out%20the%20row%20and%20column%20headers%20in%20the%20picture%20of%20the%20worksheet%2C%20but%20I%20believe%20I%20could%20count%20back%20from%20the%20cells%20that%20were%20mentioned%20in%20the%20texts.%20And%20assuming%20that%20%22the%20last%20part%20of%20the%20problem%22%20refers%20to%20Exercise%203%2C%20the%20formula%20in%20E52%20could%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUM(OFFSET(B7%2CE49%2CE48%2CE50-E49%2B1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2597074%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%20With%20this%20Excel%20Problem!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2597074%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1117369%22%20target%3D%22_blank%22%3E%40lanem1010%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EThis%20solves%20the%20issue%3A%3C%2FEM%3E%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%20image-alt%3D%22RajeshS_0-1627799521841.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F299715i23515536A5BC0525%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22RajeshS_0-1627799521841.png%22%20alt%3D%22RajeshS_0-1627799521841.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EFormula%20in%20cell%20H66%3A%3C%2FLI%3E%3C%2FUL%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(%24B%2466%3A%24E%2477%2CMATCH(%24H%2464%2C%24A%2466%3A%24A%2477%2C1)%2CMATCH(%24H%2465%2C%24B%2465%3A%24E%2465%2C1))%3C%2FCODE%3E%3C%2FPRE%3E%3CUL%3E%3CLI%3EArray%20(CSE)%20formula%20in%20J68%3A%3C%2FLI%3E%3C%2FUL%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%7B%3DAVERAGE(IF(A66%3AA77%3D12%2CB66%3AE77))%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CUL%3E%3CLI%3EArray%20(CSE)%20formula%20in%20J69%3A%3C%2FLI%3E%3C%2FUL%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%7B%3DMAX(IF(%24B%2465%3A%24E%2465%3D%24H%2469%2C%24B%2466%3A%24E%2477))%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CUL%3E%3CLI%3EFormula%20in%20H74%3A%3C%2FLI%3E%3C%2FUL%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUMPRODUCT((%24B%2465%3A%24E%2465%3D%24H%2471)*(%24A%2466%3A%24A%2477%26gt%3B%3D%24H%2472)*(%24A%2466%3A%24A%2477%26lt%3B%3D%24H%2473)*(%24B%2466%3A%24E%2477))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B.%20%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EFinish%20both%20array%20(CSE)%20formula%20with%20Ctrl%2BShift%2BEnter.%3C%2FLI%3E%3CLI%3EAdjust%20cell%20references%20as%20needed.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2597054%22%20slang%3D%22en-US%22%3ERE%3A%20Please%20Help%20With%20this%20Excel%20Problem!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2597054%22%20slang%3D%22en-US%22%3Ewhen%20you%20are%20replying%20you%20can%20open%20full%20text%20editor%20%26amp%3B%20it%20will%20allow%20you%20to%20attach%20a%20file%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2597045%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%20With%20this%20Excel%20Problem!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2597045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F258847%22%20target%3D%22_blank%22%3E%40chahine%3C%2FA%3E%3C%2FP%3E%3CP%3EHey%2C%20It's%20not%20letting%20me%20send%20it%20over%20this%20chat%2C%20but%20I%20would%20love%20to%20send%20it%20to%20you%20over%20email...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2597040%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%20With%20this%20Excel%20Problem!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2597040%22%20slang%3D%22en-US%22%3Ecan%20you%20provide%20excel%20file%20please%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2601073%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%20With%20this%20Excel%20Problem!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2601073%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much!%3C%2FLINGO-BODY%3E
New Contributor

Hi,

I am trying to solve this last part to the problem and cannot seem to find a correct answer. I have attached both screenshots to show what I am dealing with. I would love an answer that includes the formula! Thank you!Help 1.PNGHelp 2.PNG

6 Replies
can you provide excel file please?

@chahine

Hey, It's not letting me send it over this chat, but I would love to send it to you over email...

when you are replying you can open full text editor & it will allow you to attach a file

@lanem1010 

This solves the issue:

 

RajeshS_0-1627799521841.png

 

  • Formula in cell H66:
=INDEX($B$66:$E$77,MATCH($H$64,$A$66:$A$77,1),MATCH($H$65,$B$65:$E$65,1))
  • Array (CSE) formula in J68:
{=AVERAGE(IF(A66:A77=12,B66:E77))}
  • Array (CSE) formula in J69:
{=MAX(IF($B$65:$E$65=$H$69,$B$66:$E$77))}
  • Formula in H74:
=SUMPRODUCT(($B$65:$E$65=$H$71)*($A$66:$A$77>=$H$72)*($A$66:$A$77<=$H$73)*($B$66:$E$77))

 

N.B.

  • Finish both array (CSE) formula with Ctrl+Shift+Enter.
  • Adjust cell references as needed.

 

best response confirmed by lanem1010 (New Contributor)
Solution

@lanem1010 Since your assignment explicitly requires the use of OFFSET, I guess you are not really interested in solutions that do not include this function.

 

You left out the row and column headers in the picture of the worksheet, but I believe I could count back from the cells that were mentioned in the texts. And assuming that "the last part of the problem" refers to Exercise 3, the formula in E52 could be:

=SUM(OFFSET(B7,E49,E48,E50-E49+1))

 

 

Thank you so much!