Hey guys,
I need your help to devise a Dynamic Histogram using a formula based solution or a VBA based solution.
Underneath are a few idea options I'm looking for:
1. A formula that can perform the frequency results that a Histogram would do using Frequency function along with a Subtotal function element that updates when filters are on or off.
Formula Example:
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(BinVal1,ROW(BinVal1)-ROW(M4),0,1)),BinVal1),IF(SUBTOTAL(3,OFFSET(BinVal1,ROW(BinVal1)-ROW(M4),0,1)),BinVal1))>0,1))
OR
2. A VBA code that creates and updates a histogram results when the BIN Values in Range(M5:M12) are updated would be awesome.
Currently, I have a Frequency Formula in Range (O5:O13) that moves with linked data that work when the there are no filters on, but I need something that updates according to the filter scope. Range(J6:J8) is linked to Range (M5:M12) and produces the BIN Values Section when filters are on or off. So, I need the Histogram type solution to be able to calculated based on those dynamics data, but with accurate results.
Attached is the XL File for a the visuals.
I really appreciate your help on this.
I need your help to devise a Dynamic Histogram using a formula based solution or a VBA based solution.
Underneath are a few idea options I'm looking for:
1. A formula that can perform the frequency results that a Histogram would do using Frequency function along with a Subtotal function element that updates when filters are on or off.
Formula Example:
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(BinVal1,ROW(BinVal1)-ROW(M4),0,1)),BinVal1),IF(SUBTOTAL(3,OFFSET(BinVal1,ROW(BinVal1)-ROW(M4),0,1)),BinVal1))>0,1))
OR
2. A VBA code that creates and updates a histogram results when the BIN Values in Range(M5:M12) are updated would be awesome.
Currently, I have a Frequency Formula in Range (O5:O13) that moves with linked data that work when the there are no filters on, but I need something that updates according to the filter scope. Range(J6:J8) is linked to Range (M5:M12) and produces the BIN Values Section when filters are on or off. So, I need the Histogram type solution to be able to calculated based on those dynamics data, but with accurate results.
Attached is the XL File for a the visuals.
I really appreciate your help on this.