Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need some formula help please
i have 2 formulas i would like to combine, unless somebody has a better
idea. i want to sum every other row in a column =SUM(IF(MOD(ROW(H$5:H$28),2)=0,H$5:H$28,0)) this will sum all even rows but some may have n/a in them: =SUM(IF(ISNA(H5:H28),0,H5:H28)) this will sum all rows that don't contain n/a i am looking for a formula that will sum every other row, but skip n/a's. any help appreciated -- Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need some formula help please
try SUMPROD
this multiply each argument (element by element) and finaly add all the elementary results for example SUMPROD({1;2;3};{1;2;3}) = 1*1 + 2*2 + 3*3 NOTICE: a logical result is not a number, so for example you must add --ISNA(range) ( the double -- at the begining makes FALSE becomes the number 0 and TRUE becomes the number 1; it's not the only way you may multiply the logical by 1 for example TRUE*1=1 ) example: =SUMPROD( --ISNA(A1:A6) ; --(MOD(A1:A6;2)=1) ; A1:A6) notice here I make MOD = 1 so the rows 1,3, etc. (even) will be used |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
need some formula help please
the formula will not even enter, gives me an error even when trying to enter
as an array -- Gary "abcd" wrote in message ... try SUMPROD this multiply each argument (element by element) and finaly add all the elementary results for example SUMPROD({1;2;3};{1;2;3}) = 1*1 + 2*2 + 3*3 NOTICE: a logical result is not a number, so for example you must add --ISNA(range) ( the double -- at the begining makes FALSE becomes the number 0 and TRUE becomes the number 1; it's not the only way you may multiply the logical by 1 for example TRUE*1=1 ) example: =SUMPROD( --ISNA(A1:A6) ; --(MOD(A1:A6;2)=1) ; A1:A6) notice here I make MOD = 1 so the rows 1,3, etc. (even) will be used |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
need some formula help please
sorry, translation error,
it's SUMPRODUCT and not sumprod |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
need some formula help please
and there were semicolons instead of commas. i changed them but still get
n/a for the result -- Gary "abcd" wrote in message ... sorry, translation error, it's SUMPRODUCT and not sumprod |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
need some formula help please
Hi Gary,
Try =SUM(IF(MOD(ROW(H$5:H$28),2)=0,IF(NOT(ISERROR(H$5: H$28)),$H$5:$H$28),0)) This is an array formula, so commit with CTrl-Shift-Enter. -- HTH RP (remove nothere from the email address if mailing direct) "Gary Keramidas" wrote in message ... and there were semicolons instead of commas. i changed them but still get n/a for the result -- Gary "abcd" wrote in message ... sorry, translation error, it's SUMPRODUCT and not sumprod |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
need some formula help please
check the excel help or give us the exact new formula you'd tryed the sumproduct function is supposed to work perfectly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |