help on and/or formula needed

[Fred]

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)<>YEAR(O12)),AND(YEAR(K15)<>YEAR(O12),YEAR(K16)<>YEAR(o12)),AND(K16="",YEAR(K15)<>YEAR(o12))),SUM(BB19:BB41),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(o12),year(k16)=year(o12)),sum(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

 

[Bob Phillips]

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)

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)<>YEAR(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(o12),year(k16)=year(o12)),s

 

[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)<>YEAR(O12)),AND(YEAR(K15)<>YEAR(O12),YEAR(K16)<>YEAR(O12)),AND(K16="",YEAR(K15)<>YEAR(O12))),SUM(BB19:BB41),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(O12),YEAR(K16)=YEAR(O12)),SUM(BB15:BB41),IF(OR(AND(YEAR(K15)=YEAR(O12),YEAR(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

 

[Bob Phillips]

Try it as an array formula, commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

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)<>YEAR(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(O12),YEAR(K16)=YEAR(O12)),SU

 

[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)<>YEAR(O12)),AND(YEAR(K15)<>YEAR(O12),YEAR(K16)<>YEAR(O12)),AND(K16="",YEAR(K15)<>YEAR(O12))),SUM(BB19:BB41),IF(OR(AND(K15="",YEAR(K16)=YEAR(O12)),AND(YEAR(K15)<>YEAR(O12),YEAR(K16)=YEAR(O12))),SUM(BB16:BB41),IF(AND(YEAR(K15)=YEAR(O12),YEAR(K16)=YEAR(O12)),SUM(BB15:BB41),IF(OR(AND(YEAR(K15)=YEAR(O12),YEAR(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

 

[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 *