Help with Formula

[jthoward]

Im very new to Excel so please bare with me.

=2^(LOG((INDEX(G8:G31,MATCH(0.35,L8:L32,1))),2)+(LOG((INDEX(G8:G31,MATCH(0.35,L8:L32,1)+1)),2)-LOG((INDEX(G8:G31,MATCH(0.35,L8:L32,1))),2))*(0.35-INDEX(L8:L32,MATCH(0.35,L8:L32,1)))/(INDEX(L8:L32,MATCH(0.35,L8:L32,1)+1)-INDEX(L8:L32,MATCH(0.35,L8:L32,1))))

For some reason excel keeps displaying a #N/A. What is wrong with my
equation? Any tips or suggestions?

 

[Bob Umlas]

Formula worked for me (with dummy data in L8:L32 & G8:G32) - so why not
supply these 50 values so we can test further?

Bob Umlas
Excel MVP

 

[jthoward]

For G

Size (mm)
0.0625
0.125
0.25
0.5
1
2
4
6
8
11
16
22
32
45
64
90
128
180
256
362
512
1024
2048
4096

For L (Dont know if this matters, but this column is calculated.)

Cummalitive %
1
5
10
15
17
19
24
30
39
42
50
55
61
68
77
83
86
90
93
95
96
96
96
96
100

Thanks for helping Bob

 

[jthoward]

Anyone

 

[Harlan Grove]

For G

Size (mm)
0.0625
0.125
0.25
0.5
1
2
4
6
8
11
16
22
32
45
64
90
128
180
256
362
512
1024
2048
4096

For L (Dont know if this matters, but this column is calculated.)

Cummalitive %
1
5
10
15
17
19
24
30
39
42
50
55
61
68
77
83
86
90
93
95
96
96
96
96
100

And your original formula was

=2^(LOG((INDEX(G8:G31,MATCH(0.35,L8:L32,1))),2)
+(LOG((INDEX(G8:G31,MATCH(0.35,L8:L32,1)+1)),2)
-LOG((INDEX(G8:G31,MATCH(0.35,L8:L32,1))),2))
*(0.35-INDEX(L8:L32,MATCH(0.35,L8:L32,1)))
/(INDEX(L8:L32,MATCH(0.35,L8:L32,1)+1)
-INDEX(L8:L32,MATCH(0.35,L8:L32,1))))

First off you have a mismatch problem. Your lookup range, L8:L32, is one row
longer than your value range, G8:G31. This will cause you endless grief even
if everything else worked.

Next, you claim L8:L32 are percentages, but you show them as whole numbers.
Are they in fact percentages? Or are they whole numbers? If whole numbers,
searching a range of integers for an exact match to a fractional number is
doomed to failure. When I divided L8:L32 by 100, your formula returned the
value 7.039821698 rather than #N/A.

 

[jthoward]

Harlan,

That worked perfect!

I appreciate the help