PDA

View Full Version : help with excell formula



charlb
02-20-2008, 06:03 AM
hi would like to know if what I am trying is possable here is my senario

I have a group of 5 colums in each month every colum has a amount in ? and I have 12 months now I also have a colum with the total of all the months together "M3", but now I have created check boxes for each month. What I want to do if possable is when the month is checked i want the total of the 5 colums added together and subtracted from the yearly total "M3" and if i tick a second month I want that also subtrackted from the yearly total "M3" but I want every thin displayd in one colum here is my formula so far "=IF(DI1=TRUE,SUM(DE3:DI3)-M3,0) -IF(DD1=TRUE,SUM(CZ3:DI3)-M3,0)" it doesnt work when the values of my colums is 0 is this at all possable and does this make sence.
Thanks for any help

Bob Phillips
02-20-2008, 06:17 AM
Throw us a bone to work on, an example workbook.

charlb
02-20-2008, 07:35 AM
Hope this help thank you

Bob Phillips
02-20-2008, 08:01 AM
Does this formula in DJ3 do what you want

=ROUND(SUM(IF((MOD(COLUMN($X$1:$DI$1),5)=3)*($X$1:$DI$1=FALSE),$X3:$DE3+$Y3 :$DF3+$Z3:$DG3+$AA3:$DH3+$AB3:$DI3)),2)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.

When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range.

charlb
02-20-2008, 08:13 AM
Hi thanks i got it working you were very helpfull have a good day.