IF,AND, OR I dont know

[Guest]

I need help with a formula
A1 1
A2 yes
A3 3.00
A4 10
A5 20
A6 25
A7 22
A8 30
A9 2
A10 4
I need a formula in A11 that will do the following...
If A3>0 then A10=A3*(A4:A8)
If A3=0 and(or(A1=1,A2=yes) (A4:A8)*A9,(A4:A8)*A10

Thanks in advance
Steve

 

[Norman Harker]

Hi Steve

You can't by formula or function use a formula in A11 that changes an
entry made in another cell; in your case A10.

It seems like you need a formula for A10 first and then a formula for
A11

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

If A3>0 then A10=A3*(A4:A8), should have been
If A3>0 then A11=A3*(A4:A8)
If A3=0 and(or(A1=1,A2=yes) (A4:A8)*A9,(A4:A8)*A10

Thanks!!!

 

[Norman Harker]

Hi Steve!

Try:

=IF(A3<0,"",IF(A3=0,IF(OR(A1=1,A2="yes"),SUM(A4:A8)*A9,SUM(A4:A8*A10))
,A3*SUM(A4:A8)))

Check carefully with the various alternatives as there may have been a
misunderstanding of your statement.
I like to cover the whole number scale which is why I cover A4<0. But
that's a personal thing.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

Thanks! that works. In modifying the formula to my use, is there any way to sum every 4th cell in a column other than b1+b5+b9...+bn ?

 

[Max]

One quick? way:

If the data to be summed is in col B, B1 down

Put in C1: =MOD(ROW()-1,4)

Copy C1 down col C
as many rows as there is data in col B

(this sets up a repeating series: 0,1,2,3 down col C
which we can make use of as a criteria in SUMIF)

Now put in say, D1: =SUMIF(C:C,0,B:B)
(zero is used as the criteria in SUMIF
as we want b1+b5+b9+...)

D1 will return the sum of b1+b5+b9...+bn

Similarly

: =SUMIF(C:C,1,B:B)
will return the sum of b2+b6+b10...+bn

: =SUMIF(C:C,2,B:B)
will return the sum of b3+b7+b11...+bn

and so on.

Adapt to suit.

 

[Norman Harker]

Hi Steve!

Here's a general purpose approach by Bob Philips that I found on a
Google Search:

=SUMPRODUCT((B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),4)+1=1))

The general formula is:

=SUMPRODUCT((B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),n)+m=1))
Where n is the spacing between rows to be added (in your case 4)
And m is where in the range you want to start (in your case 1)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Thanks! that works. In modifying the formula to my use, is there

any way to sum every 4th cell in a column other than b1+b5+b9...+bn ?

 

[Max]

Just to add-on a little more ..

Put in C1: =MOD(ROW()-1,4)

Just change the number "4" inside : =MOD(ROW()-1,4)
to suit the criteria of every "nth" cell in the column, if the
criteria is other than every 4th cell in col B

Play around, it's fun !

 

[Guest]

So if I wanted to sum every 4th from B51:B111, would the following would be true
=SUMPRODUCT((B51:B111)*(MOD(ROW(B51:B111)-ROW(B51),4)+51=1)
Its not working for some reaso
Thanks

 

[Norman Harker]

Hi Steve!

Use:

=SUMPRODUCT((B51:B111)*(MOD(ROW(B51:B111)-ROW(B51),4)+1=1))

Returns sum of every 4th entry from and including the first cell in
the range B51:B111

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

Great Thanks!!!!!