Over time, I've received incredible help from members of this forum. With that help I've built a workbook which meets my needs. (Thank you!)

Now I want to make it as CPU/RAM efficient as possible, given that my computer isn't hugely powerful.

Because I work with static .csv files, I've created a "template" workbook with multiple tabs. I paste the .csv files into a "Paste Data" tab in the template.

The other tabs in the workbook have about 35 columns with formulas and precedents, all of which are often involved in thousands of "Solver" iterations.*And that's the challenge... how to recalculate all the cells as quickly as possible.*

In column A of the worksheets where excel does the calculations, my formula is =if('PasteData'!A1>0,'PasteData'!A1,"")

From there, many of the other columns in theses worksheets will have formulas which begin with =if(A1="","", (rest of formula). Or sometimes I'll put the calculated # of rows in $G$3, then use =if(ROW()>$G$3,"",(rest of the formula)

So.... would changing over to dynamic range names improve calculation speed? (I've rarely used range names so I'm not familiar with their advantages/limitations).

Thanks!

]]>Now I want to make it as CPU/RAM efficient as possible, given that my computer isn't hugely powerful.

Because I work with static .csv files, I've created a "template" workbook with multiple tabs. I paste the .csv files into a "Paste Data" tab in the template.

The other tabs in the workbook have about 35 columns with formulas and precedents, all of which are often involved in thousands of "Solver" iterations.

In column A of the worksheets where excel does the calculations, my formula is =if('PasteData'!A1>0,'PasteData'!A1,"")

From there, many of the other columns in theses worksheets will have formulas which begin with =if(A1="","", (rest of formula). Or sometimes I'll put the calculated # of rows in $G$3, then use =if(ROW()>$G$3,"",(rest of the formula)

So.... would changing over to dynamic range names improve calculation speed? (I've rarely used range names so I'm not familiar with their advantages/limitations).

Thanks!

dear all,

i would like to calculate the interest for a duration of days with compounded interest on quarterly basis.

Please help me. the data sheet is attached .thanks

i would like to calculate the interest for a duration of days with compounded interest on quarterly basis.

Please help me. the data sheet is attached .thanks

I'm trying to make an overtime call out list at work. I have a list of employees (18 people) but I would like to have those who do not respond or say no be moved to the bottom of the call list to ensure that they are not called again until their name makes it back to the top. Anyone have a sheet for this already?

]]>Oh my friends - time--crunch and I'm panicked. Something due today!!

1. I have a table with random dates in column B.

2. I have another table with the week dates (we run Mon-Sun). These dates are:

3. Monday dates are in column T

4. Sunday dates are in column U

5. A Reference number to (I thought) make things easier is in column "Y" which is a concat of data. the result of which is the year "~" and the week of the year"Week"

What I need is to have a formula look at the date in column B, find the week it falls into and return the "reference" into Column G.

I have attached a spreadsheet - oh I hope someone can help.

Thank you in advance.

1. I have a table with random dates in column B.

2. I have another table with the week dates (we run Mon-Sun). These dates are:

3. Monday dates are in column T

4. Sunday dates are in column U

5. A Reference number to (I thought) make things easier is in column "Y" which is a concat of data. the result of which is the year "~" and the week of the year"Week"

What I need is to have a formula look at the date in column B, find the week it falls into and return the "reference" into Column G.

I have attached a spreadsheet - oh I hope someone can help.

Thank you in advance.

Is there a way to transfer balances between the accounts in Excel? As per image?

2017-06-25_1749.png

Also attached a file itself: https://www.excelforum.com/attachmen...1&d=1498401963

(hope it is downloadable on another end!)

2017-06-25_1749.png

Also attached a file itself: https://www.excelforum.com/attachmen...1&d=1498401963

(hope it is downloadable on another end!)

Hello everyone,

I have 2 columns of data under category 1 (Day), and 2 columns under category 2 (Night). Category 1 (Day), column A is position #1-16 and column B is a name. Category 2 (Night), column C is position #1-16, and column D is a name. The same names appear in both column B & D, but with different positions. I am trying to find a sorting function to place the appropriate names in the top 4 positions in each category giving them their highest finish, but it cannot be a duplicate name. My only rules are:

1. Always start with category 1 (Day), position #1.

2. In a case where the name is in the same position in both categories (Day & Night), that name would default to category 1 (Day).

Any help is appreciated.

Jim

I have 2 columns of data under category 1 (Day), and 2 columns under category 2 (Night). Category 1 (Day), column A is position #1-16 and column B is a name. Category 2 (Night), column C is position #1-16, and column D is a name. The same names appear in both column B & D, but with different positions. I am trying to find a sorting function to place the appropriate names in the top 4 positions in each category giving them their highest finish, but it cannot be a duplicate name. My only rules are:

1. Always start with category 1 (Day), position #1.

2. In a case where the name is in the same position in both categories (Day & Night), that name would default to category 1 (Day).

Any help is appreciated.

Jim

hi

I have answered most of my questions by searching these forums (thank you) - but I have one question I am struggling to find an answer to as search results include so many other similar questions that don't help.

Is there any relatively straight-forward way to update this formula so that it returns 1 vs #NUM! when the dates are in the same month?

=1-(DAY($A6)- 1)/DAY(EOMONTH($A6, 0))+ DATEDIF(EOMONTH($A6, 0)+1,EOMONTH( $B6,-1)+1,"m")+ DAY($B6)/DAY(EOMONTH($B6, 0))

Thank you so much!

Jen

]]>I have answered most of my questions by searching these forums (thank you) - but I have one question I am struggling to find an answer to as search results include so many other similar questions that don't help.

Is there any relatively straight-forward way to update this formula so that it returns 1 vs #NUM! when the dates are in the same month?

=1-(DAY($A6)- 1)/DAY(EOMONTH($A6, 0))+ DATEDIF(EOMONTH($A6, 0)+1,EOMONTH( $B6,-1)+1,"m")+ DAY($B6)/DAY(EOMONTH($B6, 0))

Thank you so much!

Jen

I am going a bit stir crazy trying to figure out what is going wrong with a formula im using.

The scenario.

I have a Sheet called Data, within Data I simply log Name, calls made and a date check which returns true or false.

I have a sheet called Calculation and I want to pull the number of calls if two criteria is met, one does the name of the agent match and does the date check = true.

This will allow me to input weekly stats and have the data update just by changing the date.

The problem I am having is I reference to the cells on the Data sheet from Calculation but it will only work if the cell matches. The formula is

=IF(AND(Data!G:G=TRUE, Data!B:B=A17),Data!C:C,"not found")

Data!G:G - is the Data sheet colum for date checking so if valid will = true

Data!B:B - This is the column for names of agents.

Data!C:C - This is the column where total calls are stored

Example of where this works.

If on calculation I do this on Cell A16 it will only work if the data is stored on Row 16 on Data.

If the correct matching data is not on the same row on the sheet Data then it returns false.

I hope this made sense, the gist is it can do the match but only if the row with the valid data is on the same row im running the IF statement on, on the sheet calculation.

The scenario.

I have a Sheet called Data, within Data I simply log Name, calls made and a date check which returns true or false.

I have a sheet called Calculation and I want to pull the number of calls if two criteria is met, one does the name of the agent match and does the date check = true.

This will allow me to input weekly stats and have the data update just by changing the date.

The problem I am having is I reference to the cells on the Data sheet from Calculation but it will only work if the cell matches. The formula is

=IF(AND(Data!G:G=TRUE, Data!B:B=A17),Data!C:C,"not found")

Data!G:G - is the Data sheet colum for date checking so if valid will = true

Data!B:B - This is the column for names of agents.

Data!C:C - This is the column where total calls are stored

Example of where this works.

If on calculation I do this on Cell A16 it will only work if the data is stored on Row 16 on Data.

If the correct matching data is not on the same row on the sheet Data then it returns false.

I hope this made sense, the gist is it can do the match but only if the row with the valid data is on the same row im running the IF statement on, on the sheet calculation.

Hi there,

I would like to use the text e.g. placed in cell A1 on Tab1, such as: =SOM(B3:B9),

as a formula within an other cell e.g. on an other F9 on Tab2

So

Tab1-A1 contains the text for the formula

Tab2-F9 reads/retrieves this formula and shows the result of the formula within this cell (F9) for =SOM(B3:B9) of Tab2.

Could you help me define the proper syntax?

Thanks!

PS: I'm new to this forum, so please bear with me as I might post incorrectly...

]]>I would like to use the text e.g. placed in cell A1 on Tab1, such as: =SOM(B3:B9),

as a formula within an other cell e.g. on an other F9 on Tab2

So

Tab1-A1 contains the text for the formula

Tab2-F9 reads/retrieves this formula and shows the result of the formula within this cell (F9) for =SOM(B3:B9) of Tab2.

Could you help me define the proper syntax?

Thanks!

PS: I'm new to this forum, so please bear with me as I might post incorrectly...

Hello :)

I just want to mention straight-away that I have seen lots of info on how to align duplicates on the same row but nothing about how to align duplicate data in the same column. As an example I have five jobs, each on their own row 1 - 5 (in col A). Each job has x number of parts sent for example part1, part2, part3, part4, part5, part6 but not each job will have all the same parts. I want to align the data so that all Part 1's are in Col B, all of Part 2's are in Col C and so on.

So rows 1-6 (in the image below) are BEFORE and rows 9-14 are AFTER. Ideally I would like to accomplish without having to duplicate the data - So the value in "B2" for example would move to "D2" but this might require VBA code instead of a formula - not sure. Also, I should mention that in any given data set the part totals will vary, could be a max of 4 parts per job across 20 jobs or 11 parts per job across 100 jobs & the parts themselves could be different. I assume that this would make no difference but depending on how one would attack this problem I assume that extra bit of info might be helpful.

excel col_dup_sort.PNG

Cheers!

]]>I just want to mention straight-away that I have seen lots of info on how to align duplicates on the same row but nothing about how to align duplicate data in the same column. As an example I have five jobs, each on their own row 1 - 5 (in col A). Each job has x number of parts sent for example part1, part2, part3, part4, part5, part6 but not each job will have all the same parts. I want to align the data so that all Part 1's are in Col B, all of Part 2's are in Col C and so on.

So rows 1-6 (in the image below) are BEFORE and rows 9-14 are AFTER. Ideally I would like to accomplish without having to duplicate the data - So the value in "B2" for example would move to "D2" but this might require VBA code instead of a formula - not sure. Also, I should mention that in any given data set the part totals will vary, could be a max of 4 parts per job across 20 jobs or 11 parts per job across 100 jobs & the parts themselves could be different. I assume that this would make no difference but depending on how one would attack this problem I assume that extra bit of info might be helpful.

excel col_dup_sort.PNG

Cheers!

Looking for formula to extract values in sheet named Product into Outcome Sheet if the 2 date criteria are met. Would also like a formula to extract values in sheet named Product into the sheet named Outcome_II using values extracted into the sheet named Outcome.

See sample file.

See sample file.

Hi guys,

I am looking for a formula (I'm pretty sure it's an array formula) to transfer data from one range of cells to another range based on a certain outcome of the first range of cells. I've uploaded two workbooks into this thread. The first workbook is titled "Example" and it contains an example of what I'm trying to achieve and the second workbook is titled "Working" and it's the workbook that needs assistance.

Let me briefly explain the "Example" workbook first:

On the sheet "#1 vs #16" you will notice that anytime the value in column I is greater than the value in Column H the row's font changes to red indicating that the team in the corresponding F cell has beaten the corresponding team in C cell. Column C contains team names ranked as #1 and column F contains team names ranked as #16. Look at line 4 as an example. An upset occurs, so cell P4 returns the value shown in F4, Q4 returns the value from C4, and R4 returns a combination of I4 and H4 with "-" in between. The process repeats itself across row 4 because there are more upsets in that year. You should get the picture.

Now jump to the other workbook, titled "working". Go to the sheet titled "Sheet1"; the range in question is R4:AF4 (it's highlighted). I want the same outcome as shown in "Example" workbook to occur, but there is a catch. This time around A4:M4 has additional columns that may change value (Column C and Column G). Column C may have the values 1 or 16 while Column G may have values 8 or 9. These possible changes can alter whether or not an "upset" occurs. This did not take place in the "Example" workbook.

Here are the possible situations:

If C4 = 1 and G4 = 8 or 9 and J4 > K4 = no upset

If C4 = 1 and G4 = 8 or 9 and J4 < K4 = upset

If C4 = 16 and G4 = 8 or 9 and J4 > K4 = upset

If C4 = 16 and G4 = 8 or 9 and J4 < K4 = no upset

* Range R5:AN5 represents the data in range A8:M11 as an example of how the workbook should look.

Thanks in advance!

I am looking for a formula (I'm pretty sure it's an array formula) to transfer data from one range of cells to another range based on a certain outcome of the first range of cells. I've uploaded two workbooks into this thread. The first workbook is titled "Example" and it contains an example of what I'm trying to achieve and the second workbook is titled "Working" and it's the workbook that needs assistance.

Let me briefly explain the "Example" workbook first:

On the sheet "#1 vs #16" you will notice that anytime the value in column I is greater than the value in Column H the row's font changes to red indicating that the team in the corresponding F cell has beaten the corresponding team in C cell. Column C contains team names ranked as #1 and column F contains team names ranked as #16. Look at line 4 as an example. An upset occurs, so cell P4 returns the value shown in F4, Q4 returns the value from C4, and R4 returns a combination of I4 and H4 with "-" in between. The process repeats itself across row 4 because there are more upsets in that year. You should get the picture.

Now jump to the other workbook, titled "working". Go to the sheet titled "Sheet1"; the range in question is R4:AF4 (it's highlighted). I want the same outcome as shown in "Example" workbook to occur, but there is a catch. This time around A4:M4 has additional columns that may change value (Column C and Column G). Column C may have the values 1 or 16 while Column G may have values 8 or 9. These possible changes can alter whether or not an "upset" occurs. This did not take place in the "Example" workbook.

Here are the possible situations:

If C4 = 1 and G4 = 8 or 9 and J4 > K4 = no upset

If C4 = 1 and G4 = 8 or 9 and J4 < K4 = upset

If C4 = 16 and G4 = 8 or 9 and J4 > K4 = upset

If C4 = 16 and G4 = 8 or 9 and J4 < K4 = no upset

* Range R5:AN5 represents the data in range A8:M11 as an example of how the workbook should look.

Thanks in advance!

Hi guys,

I am trying to sort weather data to allow charting and have hit a wall with getting the prevailing wind direction for a day.

{=INDEX(IF(Data!$C$2:$C$20000=A158,Data!$O$2:$O$20000),MODE(IF(Data!$C$2:$C$20000=A158,MATCH(Data!$O$2:$O$20000,Data!$O$2:$O$20000,0))))}

This formula only works when the "IF" function is true for the first value it tests otherwise it returns a False in the cell. I have verified this by selecting different dates and testing the formula.

How I think it works.....

MODE: most common number IF column C (the date logged) in the data worksheet equals the date entered into cell A158, IF TRUE; MATCH: Worksheet data column "O" is the wind direction which is logged every 30 minutes (48 values for the day, totals over 16000 rows so for ease I used 20000). The MATCH is to convert the characters to a number so the MODE function will work.

The INDEX is used to convert the numbers back to the wind direction values.

I feel I may have over complicated the formula but I am quite the novice with only the holy bible (dummies guide to.....) and the web at my fingertips.

I hope I've been clear and if I have not please ask for more info/screenshots.

P.S. I'm using Excel 2010

Thanks in advance

Pic 1.JPG

Data worksheet.JPG

]]>I am trying to sort weather data to allow charting and have hit a wall with getting the prevailing wind direction for a day.

{=INDEX(IF(Data!$C$2:$C$20000=A158,Data!$O$2:$O$20000),MODE(IF(Data!$C$2:$C$20000=A158,MATCH(Data!$O$2:$O$20000,Data!$O$2:$O$20000,0))))}

This formula only works when the "IF" function is true for the first value it tests otherwise it returns a False in the cell. I have verified this by selecting different dates and testing the formula.

How I think it works.....

MODE: most common number IF column C (the date logged) in the data worksheet equals the date entered into cell A158, IF TRUE; MATCH: Worksheet data column "O" is the wind direction which is logged every 30 minutes (48 values for the day, totals over 16000 rows so for ease I used 20000). The MATCH is to convert the characters to a number so the MODE function will work.

The INDEX is used to convert the numbers back to the wind direction values.

I feel I may have over complicated the formula but I am quite the novice with only the holy bible (dummies guide to.....) and the web at my fingertips.

I hope I've been clear and if I have not please ask for more info/screenshots.

P.S. I'm using Excel 2010

Thanks in advance

Pic 1.JPG

Data worksheet.JPG

Is it possible to transform this small chunk of code into a formula in excel:

I need the sheet to run on mobile so I need a formula, rather than the obvious VBA solution.

]]>Code:

`public static double calc(double R, uint n, double step)`

{

double sum = 0;

int counter = 0;

while (R > 0)

{

sum += a(R, 3) * (double)BinomialCoefficient(counter++ + n - 4, n - 4);

R -= step;

}

return sum;

}

I have a summary sheet that will reference an export sheet. I want to check the export sheet column G for the word "Demand". If it is there I want to display the corresponding value from the other cells in it's row.

I also need these to be listed in order with no blanks.

Export sheet

Data I need.jpg

Summary sheet

Data Summerized.jpg

]]>I also need these to be listed in order with no blanks.

Export sheet

Data I need.jpg

Summary sheet

Data Summerized.jpg