[Solved] IF NOT code for spreadsheet?

Creating a macro - Writing a Script - Using the API

[Solved] IF NOT code for spreadsheet?

Postby richjsmith » Tue Apr 22, 2008 11:16 am

Hello,
I am trying to create a formula type code for my spreadsheet which will go loop through certain cells to if if they contain a value and if they do then it will add a number to another cell, is this possible? I was thinking of an IF NOT type code but im not sure if I am correct:

z = 2
IF A2 NOT 'null' then
A35 = A35 + B(z)
z + 1

then this should loop until it gets to A70 for example

is this possible and if so how do I go about implementing this into my spreadsheet, I have not done any coding to spreadsheets before.

Thanks
Richard
Last edited by richjsmith on Tue Apr 22, 2008 1:32 pm, edited 1 time in total.
richjsmith
 
Posts: 5
Joined: Mon Apr 14, 2008 2:42 pm

Re: IF NOT code for spreadsheet?

Postby squenson » Tue Apr 22, 2008 12:31 pm

The SUMIF function should do want you want... nearly:

Let's say that range A2:A24 contains either 0 or 1, and the range B2:B24 some values. If you want to sum all the values from the range B2:B24 where the corresponding A cell on the same row contains 1, then use the formula:
=SUMIF(A2:A24; 1; B2:B24)

I am not sure that SUMIF can test 'is not NULL'' value other than using "<>0" (with double quotes) as a second parameter in the formula above.
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: IF NOT code for spreadsheet?

Postby richjsmith » Tue Apr 22, 2008 12:37 pm

im not sure that will work.. let me explain some more
for example:
COL1 W z
34 4
23 3
23 3
21 2

23 2

I need to loop through col1 and if there is a value in the cell then to take the value of the corresponding cell in W and then to add that to cell Z

does that make sense?

thanks
Richard
richjsmith
 
Posts: 5
Joined: Mon Apr 14, 2008 2:42 pm

Re: IF NOT code for spreadsheet?

Postby Villeroy » Tue Apr 22, 2008 1:19 pm

1000 is a reasonable high number to cover all the input values.
Z1: =SUMIF($A$1:$A$1000;"<>0";$W$1:$W$1000) ["<>0" includes text]

alternatively:
Z1: =SUMPRODUCT($A$1:$A$1000;$W$1:$W$1000) [adds W for all numbers <>0 in A
Z1: =SUMPRODUCT(NOT(ISBLANK($A$1:$A$1000));$W$1:$W$1000) [adds W for all non-blanks in A, including text values]
Z1: =SUMPRODUCT(ISNUMBER($A$1:$A$1000);$W$1:$W$1000) [adds W for any number in A]


Cumulate in column Z:
Z1 =IF($A1;$W1;0) [start value]
Z2 =IF($A2;$W2;0)+$Z1 [add previous if condition matches]
[copy down Z2]
$A1, $A2, ... evaluate to TRUE for all numbers <>0. Texts and blanks yield FALSE.

For the concept of "this row" and "previous value" refer to: Re: [Tutorial] Absolute, rela…
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25446
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: IF NOT code for spreadsheet?

Postby richjsmith » Tue Apr 22, 2008 1:31 pm

Z1: =SUMPRODUCT(NOT(ISBLANK($A$1:$A$1000));$W$1:$W$1000)


Thanks this works fantastically


Richard
richjsmith
 
Posts: 5
Joined: Mon Apr 14, 2008 2:42 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests