Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 01 Mar 2016 20:03:42 +0100Can I condense a formula to do calculations, get an average, and return a percent all in one cell?https://ask.libreoffice.org/en/question/65436/can-i-condense-a-formula-to-do-calculations-get-an-average-and-return-a-percent-all-in-one-cell/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!ForkTue, 01 Mar 2016 20:03:42 +0100https://ask.libreoffice.org/en/question/65436/