FORECAST, in single cell, from 4 sheets?

  • Hi to all!
    Please, help. I need single cell forecast formula from four sheets. I already have concept(formula), but I don't know how to combine formula w/o helper columns X's Y's.


    I'm stuck here: =FORECAST(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),HOW TO INSERT HERE "XY" FROM ALL 4 RESERVOIR?)
    Thank you for your help in advance.


    https://1drv.ms/x/s!ArVOOVnFRGX4g-4E1j0CApEasV2CtA

  • Re: FORECAST, in single cell, from 4 sheets?


    Hi again!


    What do you mean by "XY" here, as in ""XY" FROM ALL 4 RESERVOIR"?


    Also, what should the result be, and why?


    Regards

  • Re: FORECAST, in single cell, from 4 sheets?



    =FORECAST(x,known'x,known'y)
    I have result for one reservoir(R-5), it's in OneDrive's workbook. I'm in search for fuel height(centimetres) in reservoir. I Know Reservoir number, median temperature, and litres at 15oC. Tables from sheets R-5, R-6.... are litres at 15. When fuel is above 15, then fuel is thicker(spread)(low density), when below 15oC then thinner(compress)(having a high density)

  • Re: FORECAST, in single cell, from 4 sheets?


    Maybe this Gargantula :)


    =FORECAST(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),(INDEX(IF(D6="R-5",'R-5'!A4:B243,IF(D6="R-6",'R-6'!A4:B241,IF(D6="R-7",'R-7'!A4:B245,IF(D6="R-13",'R-13'!A4:B289,"")))),MATCH(INDEX(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),1),IF(D6="R-5",'R-5'!B4:B243,IF(D6="R-6",'R-6'!B4:B241,IF(D6="R-7",'R-7'!B4:B245,IF(D6="R-13",'R-13'!B4:B289,"")))),1),1)):(INDEX(IF(D6="R-5",'R-5'!A4:B243,IF(D6="R-6",'R-6'!A4:B241,IF(D6="R-7",'R-7'!A4:B245,IF(D6="R-13",'R-13'!A4:B289,"")))),MATCH(INDEX(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),0),IF(D6="R-5",'R-5'!B4:B243,IF(D6="R-6",'R-6'!B4:B241,IF(D6="R-7",'R-7'!B4:B245,IF(D6="R-13",'R-13'!B4:B289,"")))),1)+1,1)),(INDEX(IF(D6="R-5",'R-5'!B4:B243,IF(D6="R-6",'R-6'!B4:B241,IF(D6="R-7",'R-7'!B4:B245,IF(D6="R-13",'R-13'!B4:B289,"")))),MATCH(INDEX(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),1),IF(D6="R-5",'R-5'!B4:B243,IF(D6="R-6",'R-6'!B4:B241,IF(D6="R-7",'R-7'!B4:B245,IF(D6="R-13",'R-13'!B4:B289,"")))),1))):(INDEX(IF(D6="R-5",'R-5'!B4:B243,IF(D6="R-6",'R-6'!B4:B241,IF(D6="R-7",'R-7'!B4:B245,IF(D6="R-13",'R-13'!B4:B289,"")))),MATCH(INDEX(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),1),IF(D6="R-5",'R-5'!B4:B243,IF(D6="R-6",'R-6'!B4:B241,IF(D6="R-7",'R-7'!B4:B245,IF(D6="R-13",'R-13'!B4:B289,"")))),1)+1)))
    Where :( there is : and (

  • Re: FORECAST, in single cell, from 4 sheets?


    Ok, but I don't think anyone on this forum will be prepared to decipher that monster! :-)


    When I said "Also, what should the result be, and why?", I was rather hoping that you could say which values from which sheets should ultimately be included in the FORECAST function.


    Regards

  • Re: FORECAST, in single cell, from 4 sheets?


    Sheets: R-5, R-6, R-7, R-13, Factors.
    Main sheet with Forecast formula. My problem is, now this gargantula is volatile! Is it because sheet links??


    I updated my OneDrive workbook. Thanks in advance!

  • Re: FORECAST, in single cell, from 4 sheets?


    Quote from B.W.B.;778368

    Sheets: R-5, R-6, R-7, R-13, Factors.


    Ah, doesn't look like we're getting very far. Of course I'd need to know which values from those sheets, not just the sheets themselves.


    I could probably work it out using the gigantic formula you posted, but I'm sure you'll understand if I pass.


    Hope someone else picks up on this thread shortly who can help.


    Regards

  • Re: FORECAST, in single cell, from 4 sheets?


    Actually, I knew this formula, thanks to TomS Microsoft Community. I was hoping someone could know why this jumbo crazy formula is volatile. It's enough explained in uploaded workbook. Thanks anyway. God bless us all.

  • Re: FORECAST, in single cell, from 4 sheets?


    [TABLE="width: 1170"]

    [tr]


    [TD="colspan: 13"]FORECAST(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);(INDEX(IF(D6="R-5";'R-5'!A4:B243;IF(D6="R-6";'R-6'!A4:B241;IF(D6="R-7";'R-7'!A4:B245;IF(D6="R-13";'R-13'!A4:B289;""))));MATCH(INDEX(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);1);IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));1);1)):(INDEX(IF(D6="R-5";'R-5'!A4:B243;IF(D6="R-6";'R-6'!A4:B241;IF(D6="R-7";'R-7'!A4:B245;IF(D6="R-13";'R-13'!A4:B289;""))));MATCH(INDEX(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);0);IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));1)+1;1));(INDEX(IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));MATCH(INDEX(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);1);IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));1))):(INDEX(IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));MATCH(INDEX(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);1);IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));1)+1)))[/TD]

    [/tr]


    [tr]


    [/tr]


    [tr]


    [/tr]


    [tr]


    [/tr]


    [tr]


    [/tr]


    [tr]


    [/tr]


    [tr]


    [/tr]


    [tr]


    [/tr]


    [tr]


    [/tr]


    [tr]


    [/tr]


    [/TABLE]

  • Re: FORECAST, in single cell, from 4 sheets?


    There is no way! Eventually i ended up using helper cells A1:A4, it's such a Forecast formula, unable to handle array, in any way, Index, sumproduct....
    =FORECAST(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),A1:A2,A3:A4) it's working marvelously. Thanks to everyone!