Can I condense a formula to do calculations, get an average, and return a percent all in one cell?

[Fork]

Hello,

I’m trying to figure out how to get a formula to calculate check to see if the sum of two cells is falls within + or - .5 of the sum of two other cells and return the average number of cells that meet the criteria for X rows. Not every row has data entered, so the formula has to account for blank spaces. I was asked to make the result as user-friendly and as easy to look at and manipulate as possible, so I’m trying to get this all to happen in a single cell.

I’m 90% sure I’ve gotten the formula correct when using another column to hold data, but I just can’t figure out how to get it to work in a single cell (I was never much good at coding). I was trying to use COUNTIFS, to count the number of values in row K that met the criteria in other cells, but I can only get it to return TRUE, FALSE, or #VALUE.

I’m wondering how I might calculate this all in one cell (if possible), and if COUNTIFS is the way to go, or if I’m barking up the wrong tree. Thank you all so much for any help, guidance, insights, and/or advice you may offer.

If it helps, the formula I’m using currently is
=IF(AND(G2+H2>=F2+K2-0.5, G2+H2<=F2+K2+0.5), 1, IF(K2="", “”, 0))
which, in its own column, spits out 1 if the two sums are within the margin of error, 0 if they aren’t, and empty space if the only cell that might be blank is missing data. I use this for all X rows, and then I use th Average function to average the results and multiply it by 100 to turn it into a percent.

If more explanation helps:
F2 is the ultimate target I am looking for, which is usually, but not always 0. This is added to K2, which is a number a separate program suggested we use in order to reach the target. H2 is the number we actually used, and G2 is far from the target we are, and the two are added together to calculate what the ideal number to reach our target would have actually been. In essence, this formula is trying to check to see whether K2 was within a certain margin of error (+ or - .5) of that ideal number. There are various other columns (K2 - N2) that all various suggestions from different programs, but I’ve just been changing those manually for each column. If there is a way to check do the calculations and return a percentage for columns K through N, I would be completely amazed and forever in your debt, but I don’t know if that’s possible.

I’m running Ubuntu 14.04 and using LibreOffice version 4.2.8.2

Thank you all again!

[JohnSUN]

It seems to me that your task can be accomplished with a formula that’s remotely similar to this: =SUMPRODUCT(K2:K25<>"";INT(1,5-ABS(G2:G25+K2:K25-F2:F25-H2:H25)))/COUNTA(K2:K25) Perhaps the task would be easier to decide if you showed the sample data and an example of the expected result. I’m not sure that quite understood your description of various other columns (K2 - N2)

[JohnSUN]

About multiply it by 100. No need to complicate the work of Calc redundant operations. Just set the cell format Percent

[Lupp]

If you know formulae to solve your problem using a helper column then simply do so. Complicated formulae, where avoidable, are bad for many reasons. If you are only 90% sure that your formulae are correct then don’t use spreadsheets. (If this sounds too rough, this is not intended. I simply not am good enough with English.)

[rautamiekka]

No, what you wrote is errorless and correct.