Please help me with a formula

[BrianC15]

Hello,

I have a sheet with many different variables that I assign points to, and then add all the points up cumulatively in order to get a total point score for a particular row. I've been doing this by hand, and would like to try and automate more of it.

You'd be a great help if you could help me write a formula.

I'll tally up the points in columns to the far right, so I can write a formula in column AB that shows me how many points are in column I. 

I would need for the formula to be:

If I1 is between 0-.4 then the result should be 0

If I1 is between 2-4.5 then the result should be .5

If I1 is greater than 4.5 then the result should be 1

Once I have that I could adapt it for my other needs.

There may be some differences with other columns, as sometimes I subtract points for certain results of a column. So please help me with this as well:

If column J is greater than 115 then the result should be -1.5

If column J is between 80 and 115 then the result should be -1

If column J is between 40 and 80 then the result should be -.5

If column J is between 24 and 40 then the result should be 0

If column J is between 19-24 then the result should be .5

If column J is between 16-19 then the result should be 1

If column J is between 12-16 then the result should be 1.5

If column J is between 9-12 then the result should be 2

If column J is between 0-9 then the result should be 2.5

If column J is below 0 then the result should be -1

I have lots of other variants of these however I suspect if someone were to help me with those two, I could figure out how to alter them for my other columns.

I really appreciate any help!

Thanks.

[#2]

@BrianC15 Perhaps the attached workbook points you in the right direction. Creating some named ranges (yellow shaded) and perhaps som helper columns (green shaded) will make formula writing/reading easier. I've given two examples using VLOOKUP and XLOOKUP. Not sure which Excel version you are on, so the latter might not work for you.

If these (not-very-pretty) examples are missing the point completely, please provide the workbook you are working on (or a representative part of it, and without any confidential information), including the manual calculations you now want automated. So much easier to help if one has the actual schedule(s) to work with.