none
Possible to use formulas in chart series data? RRS feed

  • Question

  • Hello, I need to build chart series from manual values. This is possible but Excel accepts only raw values. When I try to use a calculated value, excel rejects the series format.

    =SERIES("Supply";(Sheet1!$A$97;Sheet1!$H$47);(Sheet3!$B$10;Sheet3!$B$10);4)
    is accepted. I need to adjust it to something like

    =SERIES("Supply";(DATEVALUE("31.12.2014");MAX(Sheet1!A:A;Sheet1!H:H));(Sheet3!$B$10/365;Sheet3!$B$10/365);4)
    this however doesn't fit. Does Excel provide a format for inline evaluating without need to use auxiliary cells?


    Thursday, January 29, 2015 8:11 AM

Answers

  • It is not possible to evaluate formulas within a Series formula. You can use Names to calculate individual values from formulas, return an array of values or use a dynamic Name to return an array of cells and use Names in Series formulas.

    However for your purposes I don't think even use of Names would help; from your first example your formulas return multiple cell areas (albeit only one cell in each area) and your second example aims to return an array with each formulas calculating each element, you'd need a Name of Names which I don't think would work.

    Even if you could do it the way you want I think it would be a bad approach. Why not make use of cells, there's no shortage of them and it would make things much easier for maintenance.

     
    Thursday, January 29, 2015 9:52 AM
    Moderator