Sum equals unrelated range of numbers

[onesojourner]

I will describe this the best I can but it's kind of obscure.

A2 subtracted from E2 and the sum of that would put into

1-7 = .1
8-12 = .2
13-17 = .25
18-22 = .3
23-27 = .4
28-32 = .5
33-37 = .6
38-42 = .7
43-47 = .75
48-52 = .8
53-57 = .9
58-60 = 1
61-67 = 1.1
68-72 = 1.2

So for example A2 is 1330, E2 is 1340 (1330-1340=10) and .2 is displayed
or A3 is 1540 E3 is 1542 and .1 is diplayed.

I need to apply this to the whole spread sheet also row 2 through infiniti.

[daddylonglegs]

Try creating a table somewhere with the lower bound of each range in one column and the value in the other, e.g. in Y2:Z15 with Y2 down being 1, 8, 13, 18 etc. and Z2 down being 0.1, 0.2, 0.25 etc.

Now you can use a simple LOOKUP formula, e.g. in F2 copied down

=LOOKUP(E2-A2,Y$2:Z$15)

copy down column

[onesojourner]

Thanks that almost solved it. I am getting a #N/A when I don't have any data in column In A or E. I need it to remain blank due to another formula.

[daddylonglegs]

This version will give you a blank unless you have two numbers in those cells

=IF(COUNT(A2,E2)=2,LOOKUP(E2-A2,Y$2:Z$15),"")

[onesojourner]

That emptied the cells but I am having a conflict with this formula

=COUNTIFS(G:G,J2,F:F,"<>")

Is there any way around it? maybe something along the lines of IF greater than .001?

[onesojourner]

I think that did it.

=COUNTIFS(G:G,J9,F:F,">.001")