# Thread: out of my depth. Can anyone help?

1. ## out of my depth. Can anyone help?

Hello,
I struggle with math and have a problem. I'm making a spreadsheet for work so I can easily convert one figure to another. The thing is, the two columns of data I have (24 pairs of numbers) don't run parallel on a graph. I have some learning difficulties which irritatingly enough mean I can kind of see what I'm doing, but can't find the missing piece. grr!

The data is as follows:

1 = 3
2 = 5
3 = 7
4 = 9
5 =12
6=14
7=16
8=18
9=21
10=23
11=25
12=27
13=30
14=32
15=34
16=36
17=39
18=41
19=43
20=45
21=48
22=50
23=52
24=54

I had a conversion kind of figured out at 0.33 or 4.33 (i think) but it doesn't work for all the values. I have a GCSE/Highschool C and I haven't used it much in 15 years. I put it to the forum and cross my fingers.

Thanks,

Dave

2. Hi pipoldchap,

I'm not sure what your question is, but the data is pretty much linear.

I ran a linear regression model on a TI-84 calculator and came up with this linear function:

y = 2.243478261x + .4565217391

with a correlation coefficient of .999

Does this help at all?

3. Another thing you could do is....

Since the numbers increase by 2 until every fourth one,
when the increase is 3

and the numbers would be 2n+1 if the increase by 3 was not part of the pattern,
then you need the "floor" of

$2n+1+\frac{n-1}{4}$

which we can find using

$\frac{8n+4+n-1}{4}=\frac{9n+3}{4}$

To get the floor of this, just divide and discard the decimal part.

4. Wow. I'm further in further over my head than I thought.

Basically, it's a conversion table for work and I'm trying to put the info into a spreadsheet so that I can just enter the details and find out if i'm over quantity or not. I know What I want to do, I'm just damned if I know HOW!

I'll try entering them into a spreadsheet and see if it likes me today. Office and I have a tempestuous relationship at best. Thanks guys. I'm really glad this stuff makes sense to you.

I doff my cap to you both.

Thanks again,

Dave

5. Hi guys,
I entered the following equation in the spreadsheet (a2 represents the input data) and it worked for the first two but not the third onwards. I didn't understand this floor thing though...
=(((2*A2)+1)+((A2-1)/4))

Also, I entered the y value as a conversion factor and it didn't work either. I'm sure I'm just entering it wrong.

6. Originally Posted by pipoldchap
Hi guys,
I entered the following equation in the spreadsheet (a2 represents the input data) and it worked for the first two but not the third onwards. I didn't understand this floor thing though...
=(((2*A2)+1)+((A2-1)/4))

Also, I entered the y value as a conversion factor and it didn't work either. I'm sure I'm just entering it wrong.
Hi pipoldchap,

the idea with the formula is that you must discard the fraction or the part
behind the decimal point.

If you can do that then the formula converts the values.

The "floor" means the natural number below your value
or the value itself if the answer is a natural number.

For example...

n=1

$\frac{9n+3}{4}=\frac{12}{4}=3$

n=2

$\frac{18+3}{4}=\frac{21}{4}=5.25\ or\ 5\frac{1}{4}$

n=3

$\frac{27+3}{4}=\frac{30}{4}=7.5$

n=4

$\frac{36+3}{4}=\frac{39}{4}=9.75$

n=5

$\frac{45+3}{4}=\frac{48}{4}=12$

and so on....

7. Originally Posted by pipoldchap
Hello,
I struggle with math and have a problem. I'm making a spreadsheet for work so I can easily convert one figure to another. The thing is, the two columns of data I have (24 pairs of numbers) don't run parallel on a graph. I have some learning difficulties which irritatingly enough mean I can kind of see what I'm doing, but can't find the missing piece. grr!

The data is as follows:

1 = 3
2 = 5
3 = 7
4 = 9
5 =12
6=14
7=16
8=18
9=21
10=23
11=25
12=27
13=30
14=32
15=34
16=36
17=39
18=41
19=43
20=45
21=48
22=50
23=52
24=54

I had a conversion kind of figured out at 0.33 or 4.33 (i think) but it doesn't work for all the values. I have a GCSE/Highschool C and I haven't used it much in 15 years. I put it to the forum and cross my fingers.

Thanks,

Dave
Hi Dave,

I think you need less math and more spreadsheet. I'm assuming you are using Microsoft Excel.

Suppose your 24 pairs of numbers are in cells A1:B24. If you have a number from 1 to 24 in cell C26, for example, that you want mapped to its corresponding value from column B, then enter this formula in the cell where you want the value:

=VLOOKUP(C26, $A$1:$C$24, 2)

If it's possible you value might not be one of the numbers 1-24 and you want to get an error message in that case, enter

=VLOOKUP(C26, $A$1:$C$24, 2,false)

8. BRILLIANT!!!!!!!!!!!!!! Thank you so much. I didn't reliase that was possible on a spreadsheet. Witchcraft!

That's just what I needed it to do. I really appreciate you guys' help. Thank you!

dave