IF, AND, OR statements

[rcaustin]

Hi I am working on an excel worksheet for work and I need some help please. To give you a quick background on what I want out of the sheet is this... I have 31 rows of information... one row for each day of the month. These rows repeat themselves with different information for each oil well in my area so my sheet is extreemly long... 61881 cells to be exact. So basically I am comparing the K column to cell O1 which i can type in wahtever number i want. And if any of hte days in the month are true every day in the month for that well turns true. The problem is is that I can only have 30 terms in the OR statement and I have 31 days in the month of Jan...etc. Below is the current formula but it is missing the last K term.... K33=$O$1 for the last day in the month.

=IF(OR(K3=$O$1,K4=$O$1,K5=$O$1,K6=$O$1,K7=$O$1,K8=$O$1,K9=$O$1,
K10=$O$1,K11=$O$1,K12=$O$1,K13=$O$1,K14=$O$1,K15=$O$1,
K16=$O$1,K17=$O$1,K18=$O$1,K19=$O$1,K20=$O$1,K21=$O$1,
K22=$O$1,K23=$O$1,K24=$O$1,K25=$O$1,K26=$O$1,K27=$O$1,
K28=$O$1,K29=$O$1,K30=$O$1,K31=$O$1,K32=$O$1),"TRUE")

So do i need to add another OR statement or and IF statement or an AND statement to make this work?

Also is there any way possible to be able to have another cell where i can type in the number of days in the month and then it puts that many terms into my formula or is that not possible? Because each month i get sent this huge 60 000 row report and I am going to copy and past this formula for each month... the other problem is that the number of days in the month change... so i will have to alter my formula over and over each month.

Any help is much appreciated.

Thanks,
Ryan

 

[Brianwarnock]

I would use

=IF(ISNA(MATCH(O1,K1:K31,0))=FALSE,TRUE,FALSE)

The match returns #N/A if a match is not found the ISNA gives that a True response so the If switches the response.

Brian

Decided on a quick test and came up with
=NOT(ISNA(MATCH($O$1,K$1:K$31,0)))
this gives True if a match is found.
Thought the aircode looked a bit convoluted.

Edit 2 Just realise that you were entering a TEXT TRUE not a boolean therfore us the If version with "
=IF(ISNA(MATCH(O1,K1:K31,0))=FALSE,"TRUE","FALSE")

 

[rcaustin]

I would use

=IF(ISNA(MATCH(O1,K1:K31,0))=FALSE,TRUE,FALSE)

The match returns #N/A if a match is not found the ISNA gives that a True response so the If switches the response.

Brian

Decided on a quick test and came up with
=NOT(ISNA(MATCH($O$1,K$1:K$31,0)))
this gives True if a match is found.
Thought the aircode looked a bit convoluted.

Edit 2 Just realise that you were entering a TEXT TRUE not a boolean therfore us the If version with "
=IF(ISNA(MATCH(O1,K1:K31,0))=FALSE,"TRUE","FALSE")

Perfect, yup the second one works perfectly with the IF statement. Thank you for helping me!

One last question for you... is there an easy way to copy this down my 60000 rows? if I just do the simple fill down or highlight and drag down it changes the k1, k31 to k2, k32 etc but i need the first 31 to be k1 k31 then the next 31 to be k33 k64 and the next to be k65 k96 etc.? or do i have to do the old highlight the 31 rows copy past all the way down? It works if i do the old copy past for each 31 cells... but with 60000 cells it is going to take me all day!

If there is any easy way to do this that would be great if not a simple no would be suffice and I will suck it up and get it done.

Thanks,
Ryan

 

[Brianwarnock]

You can use absolute addressing or even a named range to avoid the problem mentioned as you copy down,but yes you will need to alter at each change point. but the real problem is the variable months which you will need to tackle each time, I don't have a solution off hand and bed bekons, my thoughts run to writing code or just maybe setting up the 60000 lines of formula in 4 columns of another spread sheet 28 days, 29 30 and 31 to represent the 4 lengths of months and then copy and paste the correct one into the sheet each time. Sorry that I cannot be more helpful.

Brian