Price Quality Matrix

[mr_hiboy]

Hi,

I'm trying to create a price/quality matrix, started off thinking it was easy, but hit a brick wall!

If you imagine this dataset, a list of competitors, a price which would set the Y axis and Quality score that would be the X axis.

E.g. Comp2 would be lower on the price and quality scales.

Any help would appreicated!
Paul

Competitor Price Quality
Comp1 0 -5
Comp2 100 -3
Comp3 150 -1
Comp4 200 1
Comp5 175 4
Comp6 125 5

 

[Luke M]

How's this look?

 

[NARAYANK991]

Hi Paul ,

If a higher positive value of quality is better , then you could use a bubble chart where the bubble size would be based on the quality score.

The Excelhero group on LinkedIn had the following :

http://www.linkedin.com/groups/5D-B...f200b2add69&trk=groups_items_see_more-0-b-ttl

Narayan

 

[mr_hiboy]

Thanks guys.

With a tweak or two Luke's example will work.

Ideally I'd like to set the Y axis maximum scale to based on a value set in cell A13 for example.

Also I've removed all gridlines, but again I'd like to set one line based on data in cell A14 (basically a mid point), so we get four quadrants.

Assuming it'll have to be done using vba?

Cheers
Paul

 

[Luke M]

Setting the max value (which does take VBA):
http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html
or if you want to avoid VBA:
http://peltiertech.com/WordPress/calculate-nice-axis-scales-in-your-excel-worksheet/

The gridlines could be done by just plotting some extra series (think of it as a line with coordinates at (2,0) and (2,10) to draw a line at the 2 mark on X axis.)

 

[mr_hiboy]

Hi Luke,

Thanks for the pointer.

This code is setting the x axis, not Y, where am I going wrong?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$B$26"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.MaximumScale = Target.Value
Case Else
End Select
End Sub

 

[SirJB7]

Hi, mr_hiboy!
Tried changing xlCategory by xlValue (or if a 3D chart by xlSeriesAxis)?
Also check the Chart.Axes built-in help.
Regards!

 

[mr_hiboy]

Spent 30 mins looking for that simple answer! cheers

 

[SirJB7]

Hi, mr_hiboy!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
PS: Long time ago when you last visited your ophthalmologist?