Creating Data Point on Line chart where 2 Y Axis' cross over

[groitblat]

I've created a data set that has
Fat Cal's burned
Carb Cals burned
Across heart rate number(s)

Create a line chart with 2 y Axis (Fat & Carb) across the Heartrate data. The only data point I want to print on the line chart is what heart rate level do the 2 y axis' cross?

I'm thinking that I may have to do this 'calculation' off the raw data as an intercept calculation but trying to see if there's another way to have the chart populate the cross over.

Data looks like this:
cho kcal/hr 22.93% 34.07% 38.29% 41.65% 43.39% 67.54% 82.19% 45.57%
fat kcal/hr 77.35% 66.29% 62.10% 58.76% 57.04% 33.07% 18.52% 54.88%
Heart Rate 86.5 98.0 113.1 127.4 140.7 155.3 164.7 107.0

Thanks in advance -

gina

 

[SkipVought]

Hi,

You have one y-axis with TWO series, fat & carb as percent and the other y-axis is heart rate. You do not mention any x-axis.

Also don't understand kcal/hr does not result is a percentage???

You're going to have to approximate the heart rate value IMHO.

 

[groitblat]

Sorry Y axis' points are Carbs & Fat....trying to find at what HR %'s swap aka more Carb burning than Fat burning. It's a visual result for individuals post completing a fitness test.

The % are calculated off raw data of total calories burned vs type of calories...

Yes it will be an approximate HR calculation there are other data points that relate to this cross over point as well.

Thank you

 

[mintjulep]

It looks like carb burn % + fat burn % = 100%.

So you don't have two independent series. Either one is fully sufficient to calculate the other from.

The cross-over point will be where both = 50%

 

[xlhelp]

Have you looked at the INTERCEPT function in Excel?


Never miss an opportunity to shut up

 

[groitblat]

Been playing with the intercept function since these percentages are calculated out of a massive selection of collected data every 15 seconds of a test protocol so the obvious 50/50 split doesn't just jump right now.

Intercept only seems to work of 2 data points but guess I could find the intercept data and then extrapolate out the data point of HR where this occurs based on time stamp.

Thanks

- gina

 

[SkipVought]

We'll wouldn't your 2 points be where point a < 50 and point b > 50. Obviously if a point = 50, yer there.

 

[groitblat]

Thanks SkipVought - that same realization hit me today when playing around with the raw data that I was/am over thinking the solution.
Calculating the point of time in raw data & bringing it over to the chart is the way I'm going right now

Thank you everyone for helping me to realize I was making something simple complicated

gina