Hi.

I have a formula.

Towards the end of this formula, where it says "= 11 by Col7" .... How do I write it so it grabs/equals to number 11 and also number 12?

Many thanks as always

]]>I have a formula.

HTML Code:

`=QUERY(IMPORTRANGE("1A98O9wikSyUM40aCFJfMuaT3o9KKWCVHG7eFX6Dl918","2018_MASTER_ROLL!A1:AC1390"),"SELECT Col1, Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col21 WHERE Col21 CONTAINS 'Football' and Col7 = 11 order by Col7,Col4 asc")`

Many thanks as always

I'm sorting linguistic data, and I need to count cells within a range that do NOT contain ANY of four specific entries. In addition, I need to check these against one further criterion in another column before counting them. E.g, it would be like counting lines that have an x in column A, AND do NOT have an a, b, c, OR d in column B. (And better than a simple count, I'd love to have an array of 1s and 0s telling where the conditions are met.)

I am completely new to array formulas in Excel (though have some background in other types of programming). Just as a test to see how the array calculations work, I've been trying this formula.

I set up a 'testrange' that contains the following entries:

Then I'm trying to run this formula, using ctl-shift-enter:

[code]

{=IF(testrange="B",1,0)}

[\code]

I expected to get an array that looks like this:

[0

1

0

0

0

0]

Or failing that, at least a 1, because the range contains a match.

What I get instead is just a scalar 0. If I change the formula to read

[code]

{=IF(testrange="A",1,0)}

[\code]

then I get a 1 as output. In other words, it appears to test only the first cell in the range. Is this a strange MAC issue, or just how this command works? I tried duplicating the formula alongside each of the entries, but I still got only zeros. Is anyone able to explain how I could get an array showing where the condition is true as output? In the "real world" I'm working with about 1500 lines of data spread across 6 worksheets, so I want to know what I'm doing before I start trying to interpret output.

Many thanks for your help, and as this is my first post, please graciously let me know if it's over/under detailed, or unclear!

]]>I am completely new to array formulas in Excel (though have some background in other types of programming). Just as a test to see how the array calculations work, I've been trying this formula.

I set up a 'testrange' that contains the following entries:

Code:

`A`

B

C

D

E

F

[code]

{=IF(testrange="B",1,0)}

[\code]

I expected to get an array that looks like this:

[0

1

0

0

0

0]

Or failing that, at least a 1, because the range contains a match.

What I get instead is just a scalar 0. If I change the formula to read

[code]

{=IF(testrange="A",1,0)}

[\code]

then I get a 1 as output. In other words, it appears to test only the first cell in the range. Is this a strange MAC issue, or just how this command works? I tried duplicating the formula alongside each of the entries, but I still got only zeros. Is anyone able to explain how I could get an array showing where the condition is true as output? In the "real world" I'm working with about 1500 lines of data spread across 6 worksheets, so I want to know what I'm doing before I start trying to interpret output.

Many thanks for your help, and as this is my first post, please graciously let me know if it's over/under detailed, or unclear!

Hi again everyone!

**This is posted elsewhere but I only got a Macros suitable for Windows**

I have two files Daily_Stress and SampleTest. SampleTest has column J that needs to be filled in using B and E as a reference. The Macros should pull data from Daily_Stress. Column B matches with Column A in each sheet of Daily_Stress. Column E matches with one of four Sheets in Daily_Stress. R1=R1, R2=R2, and PB1=PB1. Column J of SampleTest will ultimately be filled in with the data from the correct cell in Column E from Daily_Stress.

BUT here is the kicker and the really confusing part. I cannot have 2/2/2015 on SampleTest matching with 2/2/2015 on Daily_Stress. Instead, I need the cell connected to 2/2/2015 on SampleTest to pull in data from 2/1/2015 on the correct Daily_Stress Sheet. I am trying to list the number of tourists the day BEFORE each sample was collected.

Does this make sense? I attached the files and the first half of column J is filled in already.

Whatever Macros is created, I need it to please run on a Mac.

Thank you in advance!

**This is posted elsewhere but I only got a Macros suitable for Windows**

I have two files Daily_Stress and SampleTest. SampleTest has column J that needs to be filled in using B and E as a reference. The Macros should pull data from Daily_Stress. Column B matches with Column A in each sheet of Daily_Stress. Column E matches with one of four Sheets in Daily_Stress. R1=R1, R2=R2, and PB1=PB1. Column J of SampleTest will ultimately be filled in with the data from the correct cell in Column E from Daily_Stress.

BUT here is the kicker and the really confusing part. I cannot have 2/2/2015 on SampleTest matching with 2/2/2015 on Daily_Stress. Instead, I need the cell connected to 2/2/2015 on SampleTest to pull in data from 2/1/2015 on the correct Daily_Stress Sheet. I am trying to list the number of tourists the day BEFORE each sample was collected.

Does this make sense? I attached the files and the first half of column J is filled in already.

Whatever Macros is created, I need it to please run on a Mac.

Thank you in advance!

Kind of hard to describe. The main sheet lists songs in column A. Columns D-Z are dates marked 'x' if the song is played. Column C totals the 'x's from columns D-Z. I want to keep them listed in the order as they are. But, I want to create a bar graph which orders the songs from most played to least played.

One thing I tried: I made a sheet where columns A-C reference the main sheet. Sorting by totals in column C does not work - it instantly switches back to the order of the main sheet. I can make a filter which sorts by column C, and I can make a bar graph from that - it looks perfect. But the shared version ignores the filter, and every time I reload the sheet it clears the filter. So, the chart is only temporary.

How can I get the bar graph to stay sorted by C values?

]]>One thing I tried: I made a sheet where columns A-C reference the main sheet. Sorting by totals in column C does not work - it instantly switches back to the order of the main sheet. I can make a filter which sorts by column C, and I can make a bar graph from that - it looks perfect. But the shared version ignores the filter, and every time I reload the sheet it clears the filter. So, the chart is only temporary.

How can I get the bar graph to stay sorted by C values?

Currently using Google Spreadsheets. I have 2 sheets - "Capping" and "Join Dates" I want cell B2 on Capping to turn red if cell A2 on Join Dates doesn't have the same numerical value. Both cell B2 and A2 have formulas within them that count cells with text in the given range.

On cell B2 of Capping I tried using the "Conditional Formatting" formula "Is Not Equal To" and then referenced the cell A2 on Join Dates. Even though the 2 cells have the same numerical value, cell B2 on Capping turned red. Is this happening because the 'Is Not Equal To" formula is targeting the formula within the referenced cell, rather than its numerical value?

Kind regards,

Jimmy.

]]>On cell B2 of Capping I tried using the "Conditional Formatting" formula "Is Not Equal To" and then referenced the cell A2 on Join Dates. Even though the 2 cells have the same numerical value, cell B2 on Capping turned red. Is this happening because the 'Is Not Equal To" formula is targeting the formula within the referenced cell, rather than its numerical value?

Kind regards,

Jimmy.