Friday, January 27, 2012

yogi_Compute Points Away From Reaching A Level From A Number Of Specified Levels

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user sarebear62 said:
a formula that will subtract a particular column's value from one of the next three columns' values . ..
whichever one it is less than, but if it's greater than the 1st of the three columns and less than the 2nd, it's subtracted from that one, and so on for the third column.
The three columns are three benchmarks towards a goal, and I want to subtract the main first number (the column just before the three benchmark columns) from the most appropriate benchmark, so I know how far I have to go to reach that next benchmark.
If done it both as an array and not, both with the and's and not, simplifying it down to just if it's less than the first benchmark, subtract it from that, otherwise if it's less than the second benchmark, subtract it from that, and so on for the third.
I've ended up with parsing errors, and wrong number of arguments to iterate . . . not sure what I'm doing wrong but I'm no expert!
=ArrayFormula(IF(O2:O12P2:P12, O2:O12Q2:Q12, O2:O12 There is how I have it, in one form, the O column being how much I've accrued towards my goal, columns p, q, and r being equivalent respectively to sort of a "bronze", "silver", and "gold" goal levels, lol. I've read for a couple hours and searched but from everything I've seen I'm doing it right? So what's wrong?
----------------------------------
following is a solution to the problem