Hello every body, I'm trying to make a funtion to place in my Add In's to
save space in my actual workbook.
what I'm trying to do, it's something like this
:=IF(G10,(IF(J1=7,((SUM(A2:A7)+J1)/I1),SUM(A2:A6)+J1)/I1))),(IF(J1=7,(SUM(A2:A7)/I1),(SUM(A2:A6)I1)))).
where G=F-H,for each row.
can enyone plz. help me with this.

I J
325 6

A B C D E
F G H
75 75 40 37 76 76 1 7.70
500 575 216 404 620 696 121 7.30
900 1475 435 430 865 1561 86 5.34
500 1975 353 306 659 2220 245 5.22
300 2275 125 127 252 2472 197 4.92
150 2425 148 73 220 2693 268 5.28
150 2575 113 118 231 2924 349 5.30
250 2825 186 80 266 3190 365 4.58
300 3125 133 162 295 3485 360 3.65
300 3425 199 111 310 3795 370 2.75
250 3675 157 72 229 4023 348 1.92
200 3875 84 53 137 4161 286 1.11
75 3950 58 0 58 4218 268 None
3950 11 0 11 4229 279 now!

So far, it seems to me that in your form,ula, it doesn't matter what G1 is
since it always returns:
+ (SUM(A2:A7)+J1)/I1) when J1 is 7, and
+ (SUM(A2:A6)+J1)/I1) when J1 is not 7

The formula can therefore be replaced by:
= ( SUM(A2:A6) + (J1=7)*A7 + J1 ) / I1
explanation of (J1=7)*A7
+ if J1=7 then (J1=7) is True therefore True*A7 = A7. So A7 is added.
+ if J1<7 then (J1=7) is False therefore False*A7 = 0. So A7 is not added.

+ Why don't you give an example of the formula on a few rows.
+ When in row 1 , the formula is SUM(A2:A6) + (J1=7)*A7 + J1 ) / I1
What do you want these range to be when the formula is in row 2 ?

Regards,
Sebastien
"niteman" wrote:

Hello every body, I'm trying to make a funtion to place in my Add In's to
save space in my actual workbook.
what I'm trying to do, it's something like this
:=IF(G10,(IF(J1=7,((SUM(A2:A6)+J1)/I1))),(IF(J1=7,(SUM(A2:A7)/I1),(SUM(A2:A6)I1)))).
where G=F-H,for each row.
can enyone plz. help me with this.

I J
325 6

A B C D E
F G H
75 75 40 37 76 76 1 7.70
500 575 216 404 620 696 121 7.30
900 1475 435 430 865 1561 86 5.34
500 1975 353 306 659 2220 245 5.22
300 2275 125 127 252 2472 197 4.92
150 2425 148 73 220 2693 268 5.28
150 2575 113 118 231 2924 349 5.30
250 2825 186 80 266 3190 365 4.58
300 3125 133 162 295 3485 360 3.65
300 3425 199 111 310 3795 370 2.75
250 3675 157 72 229 4023 348 1.92
200 3875 84 53 137 4161 286 1.11
75 3950 58 0 58 4218 268 None
3950 11 0 11 4229 279 now!

Ok! what I need to get, will be the the total mark in column H for every row,
you add eather 5 rows or 6 at column H, like for row 1=7.7, 2=7.30,etc....,
now what I wanna do it's to have this formula into a funtion in a add in
file, so I can use it on diferent workbooks that I used daily so I can save
space in my hard drive by save in only what the data I need . thx.
"sebastienm" wrote:

So far, it seems to me that in your form,ula, it doesn't matter what G1 is
since it always returns:
+ (SUM(A2:A7)+J1)/I1) when J1 is 7, and
+ (SUM(A2:A6)+J1)/I1) when J1 is not 7

The formula can therefore be replaced by:
= ( SUM(A2:A6) + (J1=7)*A7 + J1 ) / I1
explanation of (J1=7)*A7
+ if J1=7 then (J1=7) is True therefore True*A7 = A7. So A7 is added.
+ if J1<7 then (J1=7) is False therefore False*A7 = 0. So A7 is not added.

+ Why don't you give an example of the formula on a few rows.
+ When in row 1 , the formula is SUM(A2:A6) + (J1=7)*A7 + J1 ) / I1
What do you want these range to be when the formula is in row 2 ?

Regards,
Sebastien
"niteman" wrote:

Hello every body, I'm trying to make a funtion to place in my Add In's to
save space in my actual workbook.
what I'm trying to do, it's something like this
:=IF(G10,(IF(J1=7,((SUM(A2:A6)+J1)/I1))),(IF(J1=7,(SUM(A2:A7)/I1),(SUM(A2:A6)I1)))).
where G=F-H,for each row.
can enyone plz. help me with this.

I J
325 6

A B C D E
F G H
75 75 40 37 76 76 1 7.70
500 575 216 404 620 696 121 7.30
900 1475 435 430 865 1561 86 5.34
500 1975 353 306 659 2220 245 5.22
300 2275 125 127 252 2472 197 4.92
150 2425 148 73 220 2693 268 5.28
150 2575 113 118 231 2924 349 5.30
250 2825 186 80 266 3190 365 4.58
300 3125 133 162 295 3485 360 3.65
300 3425 199 111 310 3795 370 2.75
250 3675 157 72 229 4023 348 1.92
200 3875 84 53 137 4161 286 1.11
75 3950 58 0 58 4218 268 None
3950 11 0 11 4229 279 now!

1. Could you, please, post the formula from a few rows (i guess the formula
is in column H, so H1 , H2, H3, ...)
2. Could you please, post some of the data for column I and J associated
with the data in A:H you have already posted in your first post. Because
without I and J, i cannot evaluate what you say: "you add eather 5 rows or 6
at column H, like for row 1=7.7, 2=7.30,etc...., "
Thanks,
Sebastien

"niteman" wrote:

Ok! what I need to get, will be the the total mark in column H for every row,
you add eather 5 rows or 6 at column H, like for row 1=7.7, 2=7.30,etc....,
now what I wanna do it's to have this formula into a funtion in a add in
file, so I can use it on diferent workbooks that I used daily so I can save
space in my hard drive by save in only what the data I need . thx.
"sebastienm" wrote:

So far, it seems to me that in your form,ula, it doesn't matter what G1 is
since it always returns:
+ (SUM(A2:A7)+J1)/I1) when J1 is 7, and
+ (SUM(A2:A6)+J1)/I1) when J1 is not 7

The formula can therefore be replaced by:
= ( SUM(A2:A6) + (J1=7)*A7 + J1 ) / I1
explanation of (J1=7)*A7
+ if J1=7 then (J1=7) is True therefore True*A7 = A7. So A7 is added.
+ if J1<7 then (J1=7) is False therefore False*A7 = 0. So A7 is not added.

+ Why don't you give an example of the formula on a few rows.
+ When in row 1 , the formula is SUM(A2:A6) + (J1=7)*A7 + J1 ) / I1
What do you want these range to be when the formula is in row 2 ?

Regards,
Sebastien
"niteman" wrote:

Hello every body, I'm trying to make a funtion to place in my Add In's to
save space in my actual workbook.
what I'm trying to do, it's something like this
:=IF(G10,(IF(J1=7,((SUM(A2:A6)+J1)/I1))),(IF(J1=7,(SUM(A2:A7)/I1),(SUM(A2:A6)I1)))).
where G=F-H,for each row.
can enyone plz. help me with this.

I J
325 6

A B C D E
F G H
75 75 40 37 76 76 1 7.70
500 575 216 404 620 696 121 7.30
900 1475 435 430 865 1561 86 5.34
500 1975 353 306 659 2220 245 5.22
300 2275 125 127 252 2472 197 4.92
150 2425 148 73 220 2693 268 5.28
150 2575 113 118 231 2924 349 5.30
250 2825 186 80 266 3190 365 4.58
300 3125 133 162 295 3485 360 3.65
300 3425 199 111 310 3795 370 2.75
250 3675 157 72 229 4023 348 1.92
200 3875 84 53 137 4161 286 1.11
75 3950 58 0 58 4218 268 None
3950 11 0 11 4229 279 now!