Modifying cell value based on contents of other cells

[r3blox]

So I really need help with this one here. Column H needs to reflect how many points you're over or under based on several criteria.

Currently, I have column F setup as follows: =IF(D8="Excellent","$99",IF(D8="Pass","$99",IF(D8="Mid","$198","")))

The ideal setup is 9 in column g, Y in columns I and J. That means you can use 9 points and break even. If you use under 9 (in column G), column H reflects the positive points you saved. If they use a Credit Card (Column K), it's minus .5 point. Column G is how many points you actually used. If they use Genie, it's +1 point, or minus one if they don't.

So, if someone puts in 9 points, uses Genie and ACH, they break even (column H shows zero). If they put in 9 points, DON'T use genie (-1), but DO use ACH, column H is minus 1. If they use 9 points, don't use genie and put it on a credit card, that's minus 1.5 points.

Hope this makes sense and thanks for the help so far everyone!

 

[Gerald Higgins]

Makes no sense at all to me and I can't see your attachment

Any chance of posting a small, representative sample of your data, together with an explanation of what you want to do exactly ?

 

[r3blox]

OK sorry, I'll try to explain better:

Referencing the attachment below:

Column H is the total column, but starts with a base value of 7.
Column G is a manual input.
Column I adds 1 to column H if it has a Y. Minus 1 if it has a N.
Column J adds 1 to column H if it has a Y, no change if blank.
Column K subtracts .5 from column H if it has a Y, no change if blank.

Finally, column H, with all of it's changes is subtracting the value of G from itself.

Example:
(Examples reference the first row)
Every H cell starts with 7 points. If there's a yes in I it's +1 (now H should total 8), if there's another Y in column J, +1 (H now totals 9). Finally, we subtract the 10 actual points from the resulting 9, so the user used 1 too many points (H totals -1).

Example:

H shows 8, but there's an "N" in column I (-1 from H) and a "Y" in column K (-.5), so H is now 6.5, then subtracting the 10 in column G, it's -4.5.

Hopefully this makes sense. I'm trying to figure out a way to track the quality of my accounts I setup for the company I work for, as it affects how I'm paid. Column H represents a multiplier of how much I'm charged or credited based on each account.

Thanks again!