Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 31st 08, 07:22 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 4
Default Variable Or Command

I am trying to figure this out... I want to do something like

=OR(K2:K(L2) <=30) where L2 is a variable number, so it would end up being

L2 = 4 (but it is the sum of several numbers used else where)
=OR(K2:K4 <=30)
and this way it checks if
K2 <= 30
K3 <= 30
K4 <= 30

And will return True if one of these is true...

I tried =OR(INDIRECT("K"&2&":K"&$L2) <=30)
Which only checks the first one and not the others

Any ideas?

  #2   Report Post  
Old October 31st 08, 07:28 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 1,358
Default Variable Or Command

Simplified your formula a little bit:
=OR(INDIRECT("K2:K"&$L2)<=30)
But your formula is correct as is, however, it is an **array** formula. When
you enter the formula, instead of just pressing the enter or tab key, you
must press CTRL+SHIFT+ENTER. You will know you entered it correctly because
it will be surrounded by the curly brackets in the formula bar { }
--
** John C **

"str83dgeboi" wrote:

I am trying to figure this out... I want to do something like

=OR(K2:K(L2) <=30) where L2 is a variable number, so it would end up being

L2 = 4 (but it is the sum of several numbers used else where)
=OR(K2:K4 <=30)
and this way it checks if
K2 <= 30
K3 <= 30
K4 <= 30

And will return True if one of these is true...

I tried =OR(INDIRECT("K"&2&":K"&$L2) <=30)
Which only checks the first one and not the others

Any ideas?

  #3   Report Post  
Old October 31st 08, 07:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 964
Default Variable Or Command


=COUNTIF(K2:INDEX(K2:K10000,L2),"<=30")0

will return TRUE if any of the values of K2:K(L2) are less than or equal to
30

it's hard to understand what you are trying to do, if this is possible what
would
you expect the result to be if all values were <=30?

--


Regards,


Peo Sjoblom

"str83dgeboi" wrote in message
...
I am trying to figure this out... I want to do something like

=OR(K2:K(L2) <=30) where L2 is a variable number, so it would end up being

L2 = 4 (but it is the sum of several numbers used else where)
=OR(K2:K4 <=30)
and this way it checks if
K2 <= 30
K3 <= 30
K4 <= 30

And will return True if one of these is true...

I tried =OR(INDIRECT("K"&2&":K"&$L2) <=30)
Which only checks the first one and not the others

Any ideas?



  #4   Report Post  
Old October 31st 08, 07:46 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default Variable Or Command

Hi,

I not exactly clear if your are just trying to return TRUE if anyone is true
or you want to sum all the elements that would return TRUE. Lets suppose the
latter

=SUM(IF(INDIRECT("K2:K"&L2)<=30,INDIRECT("K2:K"&L2 ),0))

This is array entered (Shift+Ctrl+Enter) and it sums all the item in the
range K2:Kx which are <=30.

If this helps, please click the Yes button.


--
Thanks,
Shane Devenshire


"str83dgeboi" wrote:

I am trying to figure this out... I want to do something like

=OR(K2:K(L2) <=30) where L2 is a variable number, so it would end up being

L2 = 4 (but it is the sum of several numbers used else where)
=OR(K2:K4 <=30)
and this way it checks if
K2 <= 30
K3 <= 30
K4 <= 30

And will return True if one of these is true...

I tried =OR(INDIRECT("K"&2&":K"&$L2) <=30)
Which only checks the first one and not the others

Any ideas?

  #5   Report Post  
Old November 7th 08, 08:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 4
Default Variable Or Command

I wanted to check if one is true... so here is a better example I hope.
First off there are 4 donations and if any of them have been in the last 30
days I want it to say true. So it could be 0,1,2,3,4 that are true, but I
only need to know if any of them are true

A = True or False
B = donor ID
I = donation date
K = "=TODAY()-I2", finds todays date and subtracts I's date so I get the #
of days it has been
L = "=COUNTIF(B:B, B2)" so it checks B to find how many gifts a person donated

So a table might look like
Row B K L
1 2 45 4
2 2 30
3 2 90
4 2 22
5
6 3 45 2
7 3 90
8
9 6 120 1
10
11 7 20 2
12 7 50

Now what I am having trouble doing is getting it to check Column B at the
current row L number of times

So it would be like for column A
row 1 =OR(INDIRECT(K1:K(1+(L1)))<=30)
row 6 A =OR(INDIRECT(K6:K(6+(L6)))<=30)
row 9 A =A =OR(INDIRECT(K9:K(9+(L9)))<=30)
Row 11 A =OR(INDIRECT(K11:K(11+(L11)))<=30)

I am looking for a quick way of doing this so anyone could just copy a
forumla and paste it for a new donor and not have to change anything...

idk If that was any better


"ShaneDevenshire" wrote:

Hi,

I not exactly clear if your are just trying to return TRUE if anyone is true
or you want to sum all the elements that would return TRUE. Lets suppose the
latter

=SUM(IF(INDIRECT("K2:K"&L2)<=30,INDIRECT("K2:K"&L2 ),0))

This is array entered (Shift+Ctrl+Enter) and it sums all the item in the
range K2:Kx which are <=30.

If this helps, please click the Yes button.


--
Thanks,
Shane Devenshire


"str83dgeboi" wrote:

I am trying to figure this out... I want to do something like

=OR(K2:K(L2) <=30) where L2 is a variable number, so it would end up being

L2 = 4 (but it is the sum of several numbers used else where)
=OR(K2:K4 <=30)
and this way it checks if
K2 <= 30
K3 <= 30
K4 <= 30

And will return True if one of these is true...

I tried =OR(INDIRECT("K"&2&":K"&$L2) <=30)
Which only checks the first one and not the others

Any ideas?



  #6   Report Post  
Old November 7th 08, 09:25 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 622
Default Variable Or Command

On Nov 7, 2:59*pm, str83dgeboi
wrote:
I wanted to check if one is true... so here is a better example I hope. *
First off there are 4 donations and if any of them have been in the last 30
days I want it to say true. *So it could be 0,1,2,3,4 that are true, but I
only need to know if any of them are true

A = True or False
B = donor ID
I = donation date
K = "=TODAY()-I2", finds todays date and subtracts I's date so I get the #
of days it has been
L = "=COUNTIF(B:B, B2)" so it checks B to find how many gifts a person donated

So a table might look like
Row * B * K * * L
1 * * * *2 * 45 * 4
2 * * * *2 * 30
3 * * * *2 * 90
4 * * * *2 * 22
5
6 * * * *3 * 45 * *2
7 * * * *3 * 90
8
9 * * * *6 * 120 *1
10
11 * * *7 * *20 * 2
12 * * *7 * *50

Now what I am having trouble doing is getting it to check Column B at the
current row L number of times

So it would be like for column A
row 1 =OR(INDIRECT(K1:K(1+(L1)))<=30)
row 6 A =OR(INDIRECT(K6:K(6+(L6)))<=30)
row 9 A =A =OR(INDIRECT(K9:K(9+(L9)))<=30)
Row 11 A =OR(INDIRECT(K11:K(11+(L11)))<=30)

I am looking for a quick way of doing this so anyone could just copy a
forumla and paste it for a new donor and not have to change anything...

idk If that was any better

"ShaneDevenshire" wrote:
Hi,


I not exactly clear if your are just trying to return TRUE if anyone is true
or you want to sum all the elements that would return TRUE. *Lets suppose the
latter


=SUM(IF(INDIRECT("K2:K"&L2)<=30,INDIRECT("K2:K"&L2 ),0))


This is array entered (Shift+Ctrl+Enter) and it sums all the item in the
range K2:Kx which are <=30.


If this helps, please click the Yes button.


--
Thanks,
Shane Devenshire


"str83dgeboi" wrote:


I am trying to figure this out... I want to do something like


=OR(K2:K(L2) <=30) where L2 is a variable number, so it would end up being


L2 = 4 (but it is the sum of several numbers used else where)
=OR(K2:K4 <=30)
and this way it checks if
K2 <= 30
K3 <= 30
K4 <= 30


And will return True if one of these is true...


I tried =OR(INDIRECT("K"&2&":K"&$L2) <=30)
Which only checks the first one and not the others


Any ideas?


All you need is Peo's formula, but even less than that...
=COUNTIF(K2:K1000,"<30")0
  #7   Report Post  
Old November 7th 08, 09:35 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 622
Default Variable Or Command

On Nov 7, 3:25*pm, Spiky wrote:
On Nov 7, 2:59*pm, str83dgeboi
wrote:



I wanted to check if one is true... so here is a better example I hope. *
First off there are 4 donations and if any of them have been in the last 30
days I want it to say true. *So it could be 0,1,2,3,4 that are true, but I
only need to know if any of them are true


A = True or False
B = donor ID
I = donation date
K = "=TODAY()-I2", finds todays date and subtracts I's date so I get the #
of days it has been
L = "=COUNTIF(B:B, B2)" so it checks B to find how many gifts a person donated


So a table might look like
Row * B * K * * L
1 * * * *2 * 45 * 4
2 * * * *2 * 30
3 * * * *2 * 90
4 * * * *2 * 22
5
6 * * * *3 * 45 * *2
7 * * * *3 * 90
8
9 * * * *6 * 120 *1
10
11 * * *7 * *20 * 2
12 * * *7 * *50


Now what I am having trouble doing is getting it to check Column B at the
current row L number of times


So it would be like for column A
row 1 =OR(INDIRECT(K1:K(1+(L1)))<=30)
row 6 A =OR(INDIRECT(K6:K(6+(L6)))<=30)
row 9 A =A =OR(INDIRECT(K9:K(9+(L9)))<=30)
Row 11 A =OR(INDIRECT(K11:K(11+(L11)))<=30)


I am looking for a quick way of doing this so anyone could just copy a
forumla and paste it for a new donor and not have to change anything...


idk If that was any better


"ShaneDevenshire" wrote:
Hi,


I not exactly clear if your are just trying to return TRUE if anyone is true
or you want to sum all the elements that would return TRUE. *Lets suppose the
latter


=SUM(IF(INDIRECT("K2:K"&L2)<=30,INDIRECT("K2:K"&L2 ),0))


This is array entered (Shift+Ctrl+Enter) and it sums all the item in the
range K2:Kx which are <=30.


If this helps, please click the Yes button.


--
Thanks,
Shane Devenshire


"str83dgeboi" wrote:


I am trying to figure this out... I want to do something like


=OR(K2:K(L2) <=30) where L2 is a variable number, so it would end up being


L2 = 4 (but it is the sum of several numbers used else where)
=OR(K2:K4 <=30)
and this way it checks if
K2 <= 30
K3 <= 30
K4 <= 30


And will return True if one of these is true...


I tried =OR(INDIRECT("K"&2&":K"&$L2) <=30)
Which only checks the first one and not the others


Any ideas?


All you need is Peo's formula, but even less than that...
=COUNTIF(K2:K1000,"<30")0


Ah, I wish you could delete here, sometimes. Nevermind that last post.

Here, instead you need a more advanced COUNTIF, which we usually use
SUMPRODUCT to do:
=SUMPRODUCT(--($B$2:$B$1000=B2),--($K$2:$K$1000<=30))0

I've assumed you are putting this in Column M or something and want it
to show for every line, so copy down the column. If you have a
different layout, it would have to be modified. But it should return
TRUE if any are 30 or less, FALSE if none are.
  #8   Report Post  
Old November 13th 08, 07:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 4
Default Variable Or Command

Sorry the solution was there all the time, I just had problems with copying
it in... I ended up with:

=COUNTIF($K263:INDEX($K263:K10260,$L263),"<=30")0


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table Error Message - "Command Text not set for command obje Jeff Divian Excel Discussion (Misc queries) 0 November 7th 07 10:26 PM
SUM command using variable sheets IanC Excel Worksheet Functions 3 May 18th 07 11:56 PM
variable height variable width stacked bar charts ambthiru Charts and Charting in Excel 3 January 18th 06 11:41 PM
Sum cells based on a row variable and seperate column variable CheeseHeadTransplant Excel Worksheet Functions 10 September 23rd 05 06:59 PM
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? Daniel Excel Worksheet Functions 1 July 9th 05 03:05 AM


All times are GMT +1. The time now is 09:45 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017