I'm using Excel 2016

Worksheet "**Individuals**": numeracy data from students across four year levels. Each student has a status of either "QS" or "C"

I'm trying to create tables on separate worksheets for each year level and status combination

(eg Year 4 - QS, Year 4 - C, Year 5 - QS, Year 5 - C ..... etc)

I have been able to create an array formula on worksheet "**Yr4-All **"that pulls only the year 4 students and data from worksheet "**Individuals**"

BUT ....

When I can't create an array formula in worksheet "**Yr4-QS**" that only gets the Year "**4**" students with a Status of "**QS**"

I tried to add*AND(Individuals!$C$2:$C204=4,Individuals!$D$2:$D$204="QS)* to the formula but I keep getting errors

I have attached a sample file

Any help solving this would be greatly appreciated

Worksheet "

I'm trying to create tables on separate worksheets for each year level and status combination

(eg Year 4 - QS, Year 4 - C, Year 5 - QS, Year 5 - C ..... etc)

I have been able to create an array formula on worksheet "

BUT ....

When I can't create an array formula in worksheet "

I tried to add

I have attached a sample file

Any help solving this would be greatly appreciated

Hi,

In the attached excel file I need to build a formula to give me a sum of all Paid amounts by Payment channel (for the given week):

Total paid by card:

Total paid by transfer:

Total paid in cash:

*Note: this is part of more complex spreadsheet, I have only displayed in the attached what is relevant for this question.

Thanks!

In the attached excel file I need to build a formula to give me a sum of all Paid amounts by Payment channel (for the given week):

Total paid by card:

Total paid by transfer:

Total paid in cash:

*Note: this is part of more complex spreadsheet, I have only displayed in the attached what is relevant for this question.

Thanks!

Hi,

Hope you guys can help me out here

I "borrowed" an example array for a shift schedule where X axis is date and Y axis is names. It displays the names of anyone with an X in the array for the chosen date

On my sheet, the positioning is slightly different to the example and the names column is populated by a pivot table which references a table on another sheet

Despite matching up the location references from the example array formula to my sheet, the formula refuses to display any names when there's an X in the array

The formula used is =IFERROR(INDEX($B$6:$B$30,SMALL(IF(INDEX($C$6:$AG$30,0,MATCH($B$33, $C$5:$AG$5, 0))="x",MATCH(ROW($B$6:$B$30),ROW($B$6:$B$30)),""),ROWS($A$1:A1))), "")

I've tried Ctrl Shift Enter and I've tried replacing the pivot table with a different reference table at the last column instead and referencing that instead with no luck

I've attached the example sheet I borrowed from (Shift-Schedulingv3) and my sheet (Broken Array)- I'm fairly sure the pivot table data stays intact although it doesn't have the reference page anymore

I'm using Excel 2010

Any ideas?

Hope you guys can help me out here

I "borrowed" an example array for a shift schedule where X axis is date and Y axis is names. It displays the names of anyone with an X in the array for the chosen date

On my sheet, the positioning is slightly different to the example and the names column is populated by a pivot table which references a table on another sheet

Despite matching up the location references from the example array formula to my sheet, the formula refuses to display any names when there's an X in the array

The formula used is =IFERROR(INDEX($B$6:$B$30,SMALL(IF(INDEX($C$6:$AG$30,0,MATCH($B$33, $C$5:$AG$5, 0))="x",MATCH(ROW($B$6:$B$30),ROW($B$6:$B$30)),""),ROWS($A$1:A1))), "")

I've tried Ctrl Shift Enter and I've tried replacing the pivot table with a different reference table at the last column instead and referencing that instead with no luck

I've attached the example sheet I borrowed from (Shift-Schedulingv3) and my sheet (Broken Array)- I'm fairly sure the pivot table data stays intact although it doesn't have the reference page anymore

I'm using Excel 2010

Any ideas?

I am trying to create a formula that tracks hours based on multiple things.

see the attached for example

duration hours are in A:A

I need F, G , H, or I column to populate a number if B, C, D, or E have data in it and the number needs to multiply duration by column J number

So if training has data in it then train hours should populate with the total 24

If call has data then call hours should populate with 72

is this possible?

see the attached for example

duration hours are in A:A

I need F, G , H, or I column to populate a number if B, C, D, or E have data in it and the number needs to multiply duration by column J number

So if training has data in it then train hours should populate with the total 24

If call has data then call hours should populate with 72

is this possible?

Hello,

I am trying to format a column of dates, the format should read "yyyy-mm-ddThh:mm:ss"

I tried the =TEXT formula, not getting the desired results.

Then, I am trying to write a conditional format using time. Essentially, if the date is a certain value, I want to leave it as is, but if its any other date, I want the formula to return the prior day 23:59:59

So, for example: there are #VALUE! errors in column E of Sheet 1 of my workbook. I want cell E16 to display 2019-09-19T23:59:59

Any help would be greatly appreciated.

Thanks!

I am trying to format a column of dates, the format should read "yyyy-mm-ddThh:mm:ss"

I tried the =TEXT formula, not getting the desired results.

Then, I am trying to write a conditional format using time. Essentially, if the date is a certain value, I want to leave it as is, but if its any other date, I want the formula to return the prior day 23:59:59

So, for example: there are #VALUE! errors in column E of Sheet 1 of my workbook. I want cell E16 to display 2019-09-19T23:59:59

Any help would be greatly appreciated.

Thanks!

I'm not even certain what formula function is needed to achieve the results I'm looking for, so hoping some of you excel experts out there can help.

I've attached a sample file of specimen numbers and lab information with organism values (LAB_COMPONENT_NAME) and whether they were detected or not (COMPONENT_RESULT). One specimen number might result in multiple positives as seen in the attached.

What I'm looking to do is look at the range of specimen numbers, take the first one for example, 19A-313M0005, is listed 3 times, and two organisms were detected. However for my Positive Specimen Count, I want it to look at that range of specimen numbers, if they match, then look at the COMPONENT_RESULT, and if "Detected" shows up at all (no matter how many times), only count as 1 positive specimen for that unique specimen number. Positive specimens should be 1:1, even if they result in multiple organisms, i.e. for every unique specimen number, if there is detected that shows up anywhere, whether once, twice, etc., I only want to show that the unique specimen number resulted in a positive.

Hopefully that makes sense with the example I've attached! Thanks in advance for help on this!

kdestef1

I've attached a sample file of specimen numbers and lab information with organism values (LAB_COMPONENT_NAME) and whether they were detected or not (COMPONENT_RESULT). One specimen number might result in multiple positives as seen in the attached.

What I'm looking to do is look at the range of specimen numbers, take the first one for example, 19A-313M0005, is listed 3 times, and two organisms were detected. However for my Positive Specimen Count, I want it to look at that range of specimen numbers, if they match, then look at the COMPONENT_RESULT, and if "Detected" shows up at all (no matter how many times), only count as 1 positive specimen for that unique specimen number. Positive specimens should be 1:1, even if they result in multiple organisms, i.e. for every unique specimen number, if there is detected that shows up anywhere, whether once, twice, etc., I only want to show that the unique specimen number resulted in a positive.

Hopefully that makes sense with the example I've attached! Thanks in advance for help on this!

kdestef1

Have a quantity of 2000 BOMs of varying component count that comprise each one.

Need to add the cost of the materials that roll up to that BOM at the top of the BOM grouping shown in GREEN rows. Column G has the formulas as desired, but what about the other 1994 bills of material?

How can I create a formula to SUM the material costs that belong to the assembly item in green summing up the costs into column G of the parts below it? All the fields are of different lengths. All the assemblies have blank rows between them, I can use a helper column. . . or I can get rid of the blank rows. . .All top level assemblies have a zero in column B.

TIA for your great thoughts.

Pete

Need to add the cost of the materials that roll up to that BOM at the top of the BOM grouping shown in GREEN rows. Column G has the formulas as desired, but what about the other 1994 bills of material?

How can I create a formula to SUM the material costs that belong to the assembly item in green summing up the costs into column G of the parts below it? All the fields are of different lengths. All the assemblies have blank rows between them, I can use a helper column. . . or I can get rid of the blank rows. . .All top level assemblies have a zero in column B.

TIA for your great thoughts.

Pete

I have a spreadsheet that is full of hlookups. This spreadsheet reports data results by week. So, the horizontal header is the weeks, i.e. week 1, week 2, week 3...the header for the weeks never changes and as we enter rows for each category of data, i.e. # of days, # of invoices, # of payments, they continue to pull from the particular week number we are reporting on. The first attachment is the "dashboard" that is pulling the data from the "Data" tab (second attachment).

The Rows are years, i.e 2015, 2016, 2017, etc - but the rows are separated by mini headers, i.e. # of days for week 1 in 2015. # of invoices for week 10 in 2017, etc.

Thus, we are using horizonal lookups to pull data for different categories based on week and year.

The "Data" tab (second attachment) has these categories by row and for each category, we have to go in and insert a line for the new year. So, say rows 1-10 are tracking the # of business day in the week by year. Then rows 12-20, are counting the # of employees by week (columns) by year (rows). So you can see there are different sections of data by rows. As we insert lines for each section of data (to insert the new year), it breaks the hlookups. After doing some research, I believe that the Index/Match feature might be my best bet. However, because I'm under a deadline, I don't want to spend the time learning how to roll the data if there is a better method. Can the Index and Match functions work when there are multiple sections of rows that require the new year to be inserted? Keep in mind, the week #'s never change.

If this is the correct solution to roll this spreadsheet, is there a way to easily convert the hlookups to the Index/Match formulas, so that we can pull data based on year and week for all kinds of sections of data, i.e. # of days, # of Employees, # of invoices, # of customers. I appreciate any help anyone can provide. Cheers!

Annotation 2019-12-07 134047 #3.pngAnnotation 2019-12-07 134047.png

]]>The Rows are years, i.e 2015, 2016, 2017, etc - but the rows are separated by mini headers, i.e. # of days for week 1 in 2015. # of invoices for week 10 in 2017, etc.

Thus, we are using horizonal lookups to pull data for different categories based on week and year.

The "Data" tab (second attachment) has these categories by row and for each category, we have to go in and insert a line for the new year. So, say rows 1-10 are tracking the # of business day in the week by year. Then rows 12-20, are counting the # of employees by week (columns) by year (rows). So you can see there are different sections of data by rows. As we insert lines for each section of data (to insert the new year), it breaks the hlookups. After doing some research, I believe that the Index/Match feature might be my best bet. However, because I'm under a deadline, I don't want to spend the time learning how to roll the data if there is a better method. Can the Index and Match functions work when there are multiple sections of rows that require the new year to be inserted? Keep in mind, the week #'s never change.

If this is the correct solution to roll this spreadsheet, is there a way to easily convert the hlookups to the Index/Match formulas, so that we can pull data based on year and week for all kinds of sections of data, i.e. # of days, # of Employees, # of invoices, # of customers. I appreciate any help anyone can provide. Cheers!

Annotation 2019-12-07 134047 #3.pngAnnotation 2019-12-07 134047.png

For example A1=today()

B1=text($A$1,”mmm”)

I have a table with various calculations for each month. Every month B1 changes to a dif month. I need to report the value in a given cell within that month. I could say =if(B1=“Jan”,C1,0), but I would get an error come Feb 1st. I need a formula that will display a given cell or value that will change automatically if B1 changes? I don’t think excel has an =ifs function, at least not one I know how to write. Thanks

]]>B1=text($A$1,”mmm”)

I have a table with various calculations for each month. Every month B1 changes to a dif month. I need to report the value in a given cell within that month. I could say =if(B1=“Jan”,C1,0), but I would get an error come Feb 1st. I need a formula that will display a given cell or value that will change automatically if B1 changes? I don’t think excel has an =ifs function, at least not one I know how to write. Thanks

i need help with a formula , i have a date range (I7-L31) with diferent price rates during the year , in cell C8 to E8 it should show the rates for the date you input in cell A2.

price list.xlsx

]]>price list.xlsx

Hello!

I'm working on creating reporting data around attendance for member events where non-members also attend. One thing I want to calculate is whether there are any 'first time' attendees that have never attended a previous event but within a specific date range.

For example, in the data attached there are 718 bookings that were members when they booked a ticket.

Counting the amount of unique bookings that were members equals 130.

What I want to know is of the 130 member bookings within that date range (REPORTS!B1, REPORTS!D1), how many had never attended an event previous to the start date of the date range (REPORTS!B1)

For reference, LEADS!D:D are the unique identifiers/values, LEADS!E:E are the dates, LEADS!X:X with a "Yes" means they are/were a member when booking.

Hope that makes sense?

Thank you in advance!

I'm working on creating reporting data around attendance for member events where non-members also attend. One thing I want to calculate is whether there are any 'first time' attendees that have never attended a previous event but within a specific date range.

For example, in the data attached there are 718 bookings that were members when they booked a ticket.

Counting the amount of unique bookings that were members equals 130.

What I want to know is of the 130 member bookings within that date range (REPORTS!B1, REPORTS!D1), how many had never attended an event previous to the start date of the date range (REPORTS!B1)

For reference, LEADS!D:D are the unique identifiers/values, LEADS!E:E are the dates, LEADS!X:X with a "Yes" means they are/were a member when booking.

Hope that makes sense?

Thank you in advance!

Hi ...

Trying to make a spreadsheet to record seven punters achievements during a season.

How do I get to count the hits each punter has regarding home wins, draws and away wins.

Have I started the right way, you think?

All help appreciated!

Trying to make a spreadsheet to record seven punters achievements during a season.

How do I get to count the hits each punter has regarding home wins, draws and away wins.

Have I started the right way, you think?

All help appreciated!

Hello everyone,

I am having difficulty extracting certain fields from a table based on multiple criteria. Actually, I should say "we" as several experts have had a crack at this but still haven't found the solution.

In a nutshell the current solution almost works but in some instances there is an issue. I am basically attempting to extract a program (text) and year (e.g. 2010-2011) after a status (withdrawal) from a reference table. Multiple withdrawals can occur in the same year (3 at most).

The reference table has many more rows in the my actual data set and is sorted in ascending order like in the enclosed.

Thank you to anyone who may be able to assist me!

I am having difficulty extracting certain fields from a table based on multiple criteria. Actually, I should say "we" as several experts have had a crack at this but still haven't found the solution.

In a nutshell the current solution almost works but in some instances there is an issue. I am basically attempting to extract a program (text) and year (e.g. 2010-2011) after a status (withdrawal) from a reference table. Multiple withdrawals can occur in the same year (3 at most).

The reference table has many more rows in the my actual data set and is sorted in ascending order like in the enclosed.

Thank you to anyone who may be able to assist me!

Hi,

I'm not sure if this can be done, but I'm hopeful that someone here can help me :confused:

In my spreadsheet, I have names, contract numbers and 4 item columns with dates in them. Ideally, what I would like to happen is the name cell highlights when all the item cells in the row have a value. After the oldest date in the row reaches a year, I'd like it to un-highlight the name cell and highlight the overdue date cell in a different colour. For example, when Dave's row has dates in all 4 item cells, it highlights his name cell green. When the date is 4/3/2020, cell E2 highlights in red and un-highlights Dave's name cell.

I know I'll need to use Conditional Formatting, but what formulas would I need to put in the cells to have them do this?

If this can't be done, what formulas would I use to just highlight the item cell when it has value and then un-highlight when the value is over a year old? For example, cells C2:F2 highlight when I put a date in them, but E2 un-highlights when the date is 4/3/2020

Thanks in advance for any help :)

I'm not sure if this can be done, but I'm hopeful that someone here can help me :confused:

In my spreadsheet, I have names, contract numbers and 4 item columns with dates in them. Ideally, what I would like to happen is the name cell highlights when all the item cells in the row have a value. After the oldest date in the row reaches a year, I'd like it to un-highlight the name cell and highlight the overdue date cell in a different colour. For example, when Dave's row has dates in all 4 item cells, it highlights his name cell green. When the date is 4/3/2020, cell E2 highlights in red and un-highlights Dave's name cell.

I know I'll need to use Conditional Formatting, but what formulas would I need to put in the cells to have them do this?

If this can't be done, what formulas would I use to just highlight the item cell when it has value and then un-highlight when the value is over a year old? For example, cells C2:F2 highlight when I put a date in them, but E2 un-highlights when the date is 4/3/2020

Thanks in advance for any help :)

Hey folks,

I know the title question has been answered a billion times, but I have a unique instance I can't figure out the logic on.

Basically, I have a large spreadsheet with a bunch of columns. For context, the spreadsheet contains details of what learners have completed what trainings (trainings are called "courses" on the spreadsheet), and each training is grouped by "Program". This means there can be multiple trainings in a single Program (e.g. Program 1 has 7 courses in it, Course 1, Course 2, etc.).

What I'm trying to do is give managers a clear understanding of which learners are trained on an entire program (meaning they've completed ALL courses within a program) using a % (100% means all courses are complete). I have that part down. But where I'm struggling is trying to display a list of what remaining courses a user needs to complete to be at 100%. I tried putting together an array formula that would return the nth value of any incomplete course (i.e. any course marked as In Progress or Not Yet Started) based on a user's name, but I haven't been able to make that work.

I've attached an example spreadsheet with my attempt to solve this. I have the array formula that I tried to make work on Sheet1, cell D2.

If anyone has any ideas or suggestions as to how I can make this work, I'd love to hear it. All help is appreciated!

I know the title question has been answered a billion times, but I have a unique instance I can't figure out the logic on.

Basically, I have a large spreadsheet with a bunch of columns. For context, the spreadsheet contains details of what learners have completed what trainings (trainings are called "courses" on the spreadsheet), and each training is grouped by "Program". This means there can be multiple trainings in a single Program (e.g. Program 1 has 7 courses in it, Course 1, Course 2, etc.).

What I'm trying to do is give managers a clear understanding of which learners are trained on an entire program (meaning they've completed ALL courses within a program) using a % (100% means all courses are complete). I have that part down. But where I'm struggling is trying to display a list of what remaining courses a user needs to complete to be at 100%. I tried putting together an array formula that would return the nth value of any incomplete course (i.e. any course marked as In Progress or Not Yet Started) based on a user's name, but I haven't been able to make that work.

I've attached an example spreadsheet with my attempt to solve this. I have the array formula that I tried to make work on Sheet1, cell D2.

If anyone has any ideas or suggestions as to how I can make this work, I'd love to hear it. All help is appreciated!