Any Excel experts in the house? Need some quick help.

Avatar image for sansname
Sansname

10

Forum Posts

0

Wiki Points

0

Followers

Reviews: 0

User Lists: 0

#1  Edited By Sansname

I'm not sure how to explain this so I'll try my best. I have a chart and I need to extract data from it. There are certain points marked on the chart based on the data the chart is created from, but I need to know values of points on the chart that ARE NOT marked. For example if it shows on the chart with the XY axis that 5(x) is equivalent to 7(y), I need to know what for example 6 on the x axis would be worth on the Y. I know this sounds confusing but if anyone out there knows what I'm talking about and can help me out it would be GREATLY appreciated.

Avatar image for clubsandwich
clubsandwich

3961

Forum Posts

2399

Wiki Points

0

Followers

Reviews: 0

User Lists: 9

#2  Edited By clubsandwich

I know excel, but your post is confusing me. :s

Avatar image for sansname
Sansname

10

Forum Posts

0

Wiki Points

0

Followers

Reviews: 0

User Lists: 0

#3  Edited By Sansname

Crap. Ok I'll try to make it clearer.
 
Here's some of the data the chart is based on:

0.1 - 1
0.2 - 4
0.3 - 14
0.4 - 31
0.5 - 56

And it goes on. The data on the left side is on the X axis and the data on the right is on the Y axis. With this data, I made the following chart:

caption
caption








Basically I need to know what 50 on the Y scale amounts to on the X scale. Hope this makes things clearer.

Avatar image for shotaro
shotaro

814

Forum Posts

58

Wiki Points

0

Followers

Reviews: 0

User Lists: 4

#4  Edited By shotaro

the best bet is to work out a formula for the graph - since excel uses straight lines and not curves your solution won't be particularly accurate but thankfull that makes it easier to calculate essentially from the raw data excel calculates a straight line graph between the two sets of data for example if you have

 X - Y
 4 - 5
 6 - 7

Excel would first find the gradient of the line which is found by:
(7-5)/(6-4) which is 2/2 which is 1 and the multiple of x so now we have y=x
however by observation this doesn't work so we must use x=4 and make y=5  so the equation is y=x+1

Using the examples you gave me
since y=50 lies between the points
x=0.4 - y=31
x=0.5 - y=56
we must find the equation of the line between these points on the graph to calculate the value of x when y=50.

so the gradient is
(56-31)/(0.5-0.4) = 25/0.1 = 250 so y=250x
now since 250x when x= 0.5 does not equal 56 we must find the value to make it work - well 250*0.5 is 125, 125-56 is 69 so y=250x-69 when x=0.4 y=250(0.4)-69 which is 100-69 which is 31 so the formula works

now we work backwards and say that 250x-69=50
250x=119
x=119/250
x=0.476

not the quickest way but if you do the method correctly it will always work!

Avatar image for lemegeton
Lemegeton

812

Forum Posts

876

Wiki Points

0

Followers

Reviews: 2

User Lists: 0

#5  Edited By Lemegeton
Shotaro said:
"the best bet is to work out a formula for the graph - since excel uses straight lines and not curves your solution won't be particularly accurate but thankfull that makes it easier to calculate essentially from the raw data excel calculates a straight line graph between the two sets of data for example if you have

 X - Y
 4 - 5
 6 - 7

Excel would first find the gradient of the line which is found by:
(7-5)/(6-4) which is 2/2 which is 1 and the multiple of x so now we have y=x
however by observation this doesn't work so we must use x=4 and make y=5  so the equation is y=x+1

Using the examples you gave me
since y=50 lies between the points
x=0.4 - y=31
x=0.5 - y=56
we must find the equation of the line between these points on the graph to calculate the value of x when y=50.

so the gradient is
(56-31)/(0.5-0.4) = 25/0.1 = 250 so y=250x
now since 250x when x= 0.5 does not equal 56 we must find the value to make it work - well 250*0.5 is 125, 125-56 is 69 so y=250x-69 when x=0.4 y=250(0.4)-69 which is 100-69 which is 31 so the formula works

now we work backwards and say that 250x-69=50
250x=119
x=119/250
x=0.476

not the quickest way but if you do the method correctly it will always work!"
play brain training much, lol.
Avatar image for shotaro
shotaro

814

Forum Posts

58

Wiki Points

0

Followers

Reviews: 0

User Lists: 4

#6  Edited By shotaro
Lemegeton said:
"Shotaro said:
"the best bet is to work out a formula for the graph - since excel uses straight lines and not curves your solution won't be particularly accurate but thankfull that makes it easier to calculate essentially from the raw data excel calculates a straight line graph between the two sets of data for example if you have

 X - Y
 4 - 5
 6 - 7

Excel would first find the gradient of the line which is found by:
(7-5)/(6-4) which is 2/2 which is 1 and the multiple of x so now we have y=x
however by observation this doesn't work so we must use x=4 and make y=5  so the equation is y=x+1

Using the examples you gave me
since y=50 lies between the points
x=0.4 - y=31
x=0.5 - y=56
we must find the equation of the line between these points on the graph to calculate the value of x when y=50.

so the gradient is
(56-31)/(0.5-0.4) = 25/0.1 = 250 so y=250x
now since 250x when x= 0.5 does not equal 56 we must find the value to make it work - well 250*0.5 is 125, 125-56 is 69 so y=250x-69 when x=0.4 y=250(0.4)-69 which is 100-69 which is 31 so the formula works

now we work backwards and say that 250x-69=50
250x=119
x=119/250
x=0.476

not the quickest way but if you do the method correctly it will always work!"
play brain training much, lol.
"
actually no thats a maths degree talking.
Avatar image for lemegeton
Lemegeton

812

Forum Posts

876

Wiki Points

0

Followers

Reviews: 2

User Lists: 0

#7  Edited By Lemegeton

yeah i figured as much. kudos to you sir

Avatar image for davidpalmer
DavidPalmer

376

Forum Posts

0

Wiki Points

0

Followers

Reviews: 0

User Lists: 0

#8  Edited By DavidPalmer

Most of this flew wayy over my head... :)