|
help on and/or formula needed
I have a formula that I cannot get right and have been working on it
for several days now and would appreciate a "parenthesis" in the right place. The background : I have to calculate the total of a column based on the year of purchase of an item (depreciated over 5 years) BUT, the first 2 items only get included in the total if the column year is the year of purchase, i.e. the first year. Cells: o12:w12 contain the years to be totalled for K15/16 are the years for the first 2 items K19:K41 are the years for the remaining items BB15:BB41 are the values to be totalled all Year cells are custom yyyy format all cells to be totalled are numeric I created a grid of valid combinations as follows K15 K16 Cells totalled Blank Blank BB19:BB41 * Blank =o12 BB16:BB41 ** Blank <o12 BB19:BB41 * =o12 =o12 BB15:BB41 *** <o12 <o12 BB19:BB41 * =o12 <o12 BB19:BB41 + BB15 **** <o12 =o12 BB16:BB41 ** =o12 Blank BB19:BB41 + BB15 **** <o12 Blank BB19:BB41 * So there appear to be only 4 sets of calculations I need to make. I created the following formula but keep getting missing parenthesis errors. =IF(OR(AND(K15="",K16=""),AND(K15="",YEAR(K16)<YE AR(O12)),AND(YEAR(K15)<YEAR(O12),YEAR(K16)<YEAR( o12)),AND(K16="",YEAR(K15)<YEAR(o12))),SUM(BB19:B B41),IF(or(AND(K15="",year(K16)=year(o12)),AND(yea r(K15)<year(o12),year(k16)=year(012)),SUM(BB16:BB 41)),if(and(year(k15)=year(o12),year(k16)=year(o12 )),sum(bb15:bb41)),if(OR(AND(year(k15)=year(o12),y ear(k16)<year(o12)),and(k16="",year(k15)=year(o12 )),sum(bb19:bb41+bb15)) Any help or suggestions anyone can give would be much appreciated. TIA Fred |
I haven't even attempted to look at your formula, but could you give an
example of the source data and expected results, that is not your grid? -- HTH RP (remove nothere from the email address if mailing direct) "Fred" wrote in message oups.com... I have a formula that I cannot get right and have been working on it for several days now and would appreciate a "parenthesis" in the right place. The background : I have to calculate the total of a column based on the year of purchase of an item (depreciated over 5 years) BUT, the first 2 items only get included in the total if the column year is the year of purchase, i.e. the first year. Cells: o12:w12 contain the years to be totalled for K15/16 are the years for the first 2 items K19:K41 are the years for the remaining items BB15:BB41 are the values to be totalled all Year cells are custom yyyy format all cells to be totalled are numeric I created a grid of valid combinations as follows K15 K16 Cells totalled Blank Blank BB19:BB41 * Blank =o12 BB16:BB41 ** Blank <o12 BB19:BB41 * =o12 =o12 BB15:BB41 *** <o12 <o12 BB19:BB41 * =o12 <o12 BB19:BB41 + BB15 **** <o12 =o12 BB16:BB41 ** =o12 Blank BB19:BB41 + BB15 **** <o12 Blank BB19:BB41 * So there appear to be only 4 sets of calculations I need to make. I created the following formula but keep getting missing parenthesis errors. =IF(OR(AND(K15="",K16=""),AND(K15="",YEAR(K16)<YE AR(O12)),AND(YEAR(K15)<YE AR(O12),YEAR(K16)<YEAR(o12)),AND(K16="",YEAR(K15) <YEAR(o12))),SUM(BB19:BB4 1),IF(or(AND(K15="",year(K16)=year(o12)),AND(year( K15)<year(o12),year(k16)= year(012)),SUM(BB16:BB41)),if(and(year(k15)=year(o 12),year(k16)=year(o12)),s um(bb15:bb41)),if(OR(AND(year(k15)=year(o12),year( k16)<year(o12)),and(k16=" ",year(k15)=year(o12)),sum(bb19:bb41+bb15)) Any help or suggestions anyone can give would be much appreciated. TIA Fred |
Of course, no sooner do I post than I manage to correct the parenthesis
problem, however I end up with #VALUE! as the result. Watching the results of each section of the formula, the results look OK and the match occurs in the final IF(OR(AND set, but, even though the monitored formula shows a valid result I still get #VALUE! returned. =IF(OR(AND(K15="",K16=""),AND(K15="",YEAR(K16)<YE AR(O12)),AND(YEAR(K15)<YEAR(O12),YEAR(K16)<YEAR( O12)),AND(K16="",YEAR(K15)<YEAR(O12))),SUM(BB19:B B41),IF(OR(AND(K15="",YEAR(K16)=YEAR(O12)),AND(YEA R(K15)<YEAR(O12),YEAR(K16)=YEAR(12))),SUM(BB16:BB 41),IF(AND(YEAR(K15)=YEAR(O12),YEAR(K16)=YEAR(O12) ),SUM(BB15:BB41),IF(OR(AND(YEAR(K15)=YEAR(O12),YEA R(K16)<YEAR(O12)),AND(K16="",YEAR(K15)=YEAR(O12)) ),SUM(BB19:BB41+BB15),0)))) In K15, 2005, k16 is blank and o12 = 2005 so, less help needed than before please Fred |
Try it as an array formula, commit with Ctrl-Shift-Enter
-- HTH RP (remove nothere from the email address if mailing direct) "Fred" wrote in message oups.com... Of course, no sooner do I post than I manage to correct the parenthesis problem, however I end up with #VALUE! as the result. Watching the results of each section of the formula, the results look OK and the match occurs in the final IF(OR(AND set, but, even though the monitored formula shows a valid result I still get #VALUE! returned. =IF(OR(AND(K15="",K16=""),AND(K15="",YEAR(K16)<YE AR(O12)),AND(YEAR(K15)<YE AR(O12),YEAR(K16)<YEAR(O12)),AND(K16="",YEAR(K15) <YEAR(O12))),SUM(BB19:BB4 1),IF(OR(AND(K15="",YEAR(K16)=YEAR(O12)),AND(YEAR( K15)<YEAR(O12),YEAR(K16)= YEAR(12))),SUM(BB16:BB41),IF(AND(YEAR(K15)=YEAR(O1 2),YEAR(K16)=YEAR(O12)),SU M(BB15:BB41),IF(OR(AND(YEAR(K15)=YEAR(O12),YEAR(K1 6)<YEAR(O12)),AND(K16="", YEAR(K15)=YEAR(O12))),SUM(BB19:BB41+BB15),0)))) In K15, 2005, k16 is blank and o12 = 2005 so, less help needed than before please Fred |
Hi Bob,
Thanks for that, yes, that no longer gives the missing parenthesis error, however I end up with a result of #VALUE! =IF(OR(AND(K15="",K16=""),AND(K15="",YEAR(K16)<YE AR(O12)),AND(YEAR(K15)<YEAR(O12),YEAR(K16)<YEAR( O12)),AND(K16="",YEAR(K15)<YEAR(O12))),SUM(BB19:B B41),IF(OR(AND(K15="",YEAR(K16)=YEAR(O12)),AND(YEA R(K15)<YEAR(O12),YEAR(K16)=YEAR(O12))),SUM(BB16:B B41),IF(AND(YEAR(K15)=YEAR(O12),YEAR(K16)=YEAR(O12 )),SUM(BB15:BB41),IF(OR(AND(YEAR(K15)=YEAR(O12),YE AR(K16)<YEAR(O12)),AND(K16="",YEAR(K15)=YEAR(O12) )),SUM(BB19:BB41+BB15),0)))) K15 = 2005, K16 is blank and O12 = 2005. "Monitoring" the formula the trap is sprung in the last IF(AND(OR set, with a result of TRUE for the OR, the filan AND gives both test as TRUE, however in the monitor, it shows that Logical test = #VALUE!, "value if true" gives a valid answer, value if false gives 0, but the result in the monitor is blank and in the cell I get #VALUE! Thanks again Fred |
Hi Bob,
There appears to have been a slight hiccup in the postings, however K15 K16 Cells totalled Blank Blank BB19:BB41 * Blank =o12 BB16:BB41 ** Blank <o12 BB19:BB41 * =o12 =o12 BB15:BB41 *** <o12 <o12 BB19:BB41 * =o12 <o12 BB19:BB41 + BB15 **** <o12 =o12 BB16:BB41 ** so as examples, 1) with O12 and K15 both containing year 2005, and K16 blank, I would expect the formula to total BB19:BB41 and BB15 2) with O12 and K15 and K16 containing 2005, it should total all cells, i.e. BB15:BB41 3) where O12 & K16 contain 2005 but K15 contains contains 2006, BB16:BB41 would be totalled, BC15 (for the 2006 column) would be totalled as in example 1, where O12 and L16 (for 2006 column) are 2006, then BC19:BC41 and BC15 are totalled. Regards Fred =o12 Blank BB19:BB41 + BB15 **** <o12 Blank BB19:BB41 * |
| All times are GMT +1. The time now is 10:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com