Example files are available to registered members only... Register and login !

Author Topic: Formulas that produce an array to be used by SFE  (Read 525 times)

Offline kelliottdykes

  • Newbie
  • *
  • Posts: 2
  • Karma: +0/-0
  • SfE user
Formulas that produce an array to be used by SFE
« on: April 29, 2016, 09:31:49 PM »
Generally when I produce a chart in SFE i reference the actual values (e.g. =barchart(a1:a10)) that will produce the chart.


Is it possible to use a formula to return an array, that will then be used to produce the chart (e.g. =barchart(sumproduct(b1:b10="Thing1",a1:a10))?


I've always gotten an error when I try to insert a formula like the one in red above....but I don't know whether that is because SFE doesn't support using a formula like that....or it doesn't work because I haven't written the formula right (or i don't know the right syntax to use for SFE).


Thanks,


Elliott

Offline Fabrice

  • Moderator
  • Administrator
  • Sr. Member
  • *****
  • Posts: 135
  • Karma: +18/-0
  • Creator of Sparklines for Excel
    • Sparklines for Excel
Re: Formulas that produce an array to be used by SFE
« Reply #1 on: April 29, 2016, 09:42:14 PM »
By default, the values to be plot have to be passed in the SfE formulas as a Range (like A1:A12).
To pass an array, the VBA code would have to be adapted.


A workaround would be to save your array (resulting from your sumproduct formula) to a named dynamic range (MyDynamicRange for example) and use =barchart(MyDynamicRange).


google "named dynamic range" for examples
Fabrice Rimlinger - Creator of Sparklines for Excel
MacBook Pro Retina
Office 2011 for Mac
Virtual machine : Windows 7 - Office 2013 x32

Offline kelliottdykes

  • Newbie
  • *
  • Posts: 2
  • Karma: +0/-0
  • SfE user
Re: Formulas that produce an array to be used by SFE
« Reply #2 on: May 03, 2016, 05:18:20 PM »
Thank you Fabrice!


Very nice of you to reply  :)