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

I am trying to get excel to convert total minutes into total hours and minutes but am having some trouble.

The total minutes is 3,540 which is 59 hours. I get this number from summing all the minutes in a column.

My conversion formula is =sum(b1:b25)/1440 which gives me a result of 11:00:00.

how do i get it to display the true 59:00:00?

]]>The total minutes is 3,540 which is 59 hours. I get this number from summing all the minutes in a column.

My conversion formula is =sum(b1:b25)/1440 which gives me a result of 11:00:00.

how do i get it to display the true 59:00:00?

Hello all,

I am currently working on an Excel pre-defined template. The goal of the template is to make all sentences written about each individual combined in one. So, if there are 40 sentences about 20 people, I need a formula to combine right sentence with right names.

My problem is setting the formula properly. I am familiar working with Concatenate formula, but I need help with nesting the Concatenate formula.

Hope you understood my problem.

Best regards to all

]]>I am currently working on an Excel pre-defined template. The goal of the template is to make all sentences written about each individual combined in one. So, if there are 40 sentences about 20 people, I need a formula to combine right sentence with right names.

My problem is setting the formula properly. I am familiar working with Concatenate formula, but I need help with nesting the Concatenate formula.

Hope you understood my problem.

Best regards to all

Hi everyone :)

I've just started a little project of a monte carlo simulation for Roulette just for fun. So I've currently got it set up to play through 1000 spins and then simulate that 1000 spin game 5000 times.

What I'd like to know is if I could have a cell where I can input the number of spins the game will contain? So I could change a cell to 100, and then instead of doing the 1000 I've set it to, it would just do 100 spins instead. So a variable amount of plays depending on what a cell is set to.

Also in Roulette, you can lose all your money and so you can't play any more. Currently to factor this in I've got it to be IF the previous cell was 0, then every result from then on would be 0, but it's still processing that until the 1000 spins is over. Seems like a waste? Is there a way for it to just stop the spinning if the current bankroll becomes 0?

Thanks in advance for any helpful answers! It's the first time i've ever played round with monte carlo simulation.

]]>I've just started a little project of a monte carlo simulation for Roulette just for fun. So I've currently got it set up to play through 1000 spins and then simulate that 1000 spin game 5000 times.

What I'd like to know is if I could have a cell where I can input the number of spins the game will contain? So I could change a cell to 100, and then instead of doing the 1000 I've set it to, it would just do 100 spins instead. So a variable amount of plays depending on what a cell is set to.

Also in Roulette, you can lose all your money and so you can't play any more. Currently to factor this in I've got it to be IF the previous cell was 0, then every result from then on would be 0, but it's still processing that until the 1000 spins is over. Seems like a waste? Is there a way for it to just stop the spinning if the current bankroll becomes 0?

Thanks in advance for any helpful answers! It's the first time i've ever played round with monte carlo simulation.

Good morning all, I am trying to get the average of a column if the date in a different column is between 2 dates. I have attached a sample of the sheets I am using.

The formula is in the "KPI" tab (cells B2:B5)

I am looking for the average of column T in the data tab, if, Column Q data = Column A KPI and if the dates in column D on data are between the 2 dates in the KPI sheet (Cells K1 and L1)

Please help as google has not been my friend so far.

Thank you in advance.

Katie

The formula is in the "KPI" tab (cells B2:B5)

I am looking for the average of column T in the data tab, if, Column Q data = Column A KPI and if the dates in column D on data are between the 2 dates in the KPI sheet (Cells K1 and L1)

Please help as google has not been my friend so far.

Thank you in advance.

Katie

Hello Experts,

I want little extension for given formula ... Actually this formula helps me to find Smallest value from Different If Condition...

SMALL(IF(('Row Data'!A2:A15000='UpTrend Stocks'!A5)*('Row Data'!AD2:AD15000="OP")*('Row Data'!X2:X15000="Star Bullish"),'Row Data'!AI2:AI15000),ROW(A1))

Now I Want to add little extension in "Star Bullish" formula with "OR" Factor...

Means Row data column in my sheet has 4 Factor Star Bullish / star bearish / bullish / bearish /

I want the smallest value only if Star Bullish & Bullish condition match...

Please help me in this formula...

I had try my level best but result not seems perfect...

]]>I want little extension for given formula ... Actually this formula helps me to find Smallest value from Different If Condition...

SMALL(IF(('Row Data'!A2:A15000='UpTrend Stocks'!A5)*('Row Data'!AD2:AD15000="OP")*('Row Data'!X2:X15000="Star Bullish"),'Row Data'!AI2:AI15000),ROW(A1))

Now I Want to add little extension in "Star Bullish" formula with "OR" Factor...

Means Row data column in my sheet has 4 Factor Star Bullish / star bearish / bullish / bearish /

I want the smallest value only if Star Bullish & Bullish condition match...

Please help me in this formula...

I had try my level best but result not seems perfect...

This has been driving me crazy for a while. What I'm trying to do is use 3 different numbers and return a yes or no answer. Here is what I have:

First amount - 1

Second amount - $50,000

Third amount - 50

Based on those amounts, I want it to look at a list and see if the items on that list meets the criteria. Here is the list:

1 $20,000 25

2 $27,000 30

3 $38,000 35

4 $48,000 40

5 $66,000 50

I want it to look up the first amount, so in this case it's 1. Then using the row that the 1 is on, I want to see if the second amount ($50,000) is greater than the second amount on the row (in this case $20,000). If it is greater, then I need to see if the third amount (50) is greater than the third amount on the row (in this case 25). If all of those are true, then I want to return a "Yes". If any of them are not true, then return a "No".

I hope that makes sense. Thanks!

]]>First amount - 1

Second amount - $50,000

Third amount - 50

Based on those amounts, I want it to look at a list and see if the items on that list meets the criteria. Here is the list:

1 $20,000 25

2 $27,000 30

3 $38,000 35

4 $48,000 40

5 $66,000 50

I want it to look up the first amount, so in this case it's 1. Then using the row that the 1 is on, I want to see if the second amount ($50,000) is greater than the second amount on the row (in this case $20,000). If it is greater, then I need to see if the third amount (50) is greater than the third amount on the row (in this case 25). If all of those are true, then I want to return a "Yes". If any of them are not true, then return a "No".

I hope that makes sense. Thanks!

Anybody have this issue before with conditional formatting not working? I am using the formula in the cells and it is returning a true value but the conditional formatting is not updating. Also, if I leave out the AND part and evaluate these conditions separately they will both return true with the conditional formatting working but not together.

BTW, the formula I am using is =AND(LEN(INDIRECT("$A$" & ROW()))>0,MOD(ROW(),2) = 1)

Any thoughts?

]]>BTW, the formula I am using is =AND(LEN(INDIRECT("$A$" & ROW()))>0,MOD(ROW(),2) = 1)

Any thoughts?

I am attempting to use the "IF" formula, please help

If A2="E"; B2=".2"

If A2="T"; B2=".6"

If A2="10k"; B2=".8"

If A2="I"; B2="1"

If A2="R"; B2="1.5"

Will someone help me

]]>If A2="E"; B2=".2"

If A2="T"; B2=".6"

If A2="10k"; B2=".8"

If A2="I"; B2="1"

If A2="R"; B2="1.5"

Will someone help me

Can you alter the formula in column C to show the results in column D?

Show the corresponding name when column A is "O6," and do not show "0s" or errors.

Or is there another way to get these results?

Image for example:

formula examples.jpg

]]>Show the corresponding name when column A is "O6," and do not show "0s" or errors.

Or is there another way to get these results?

Image for example:

formula examples.jpg

is there a way to get the week numbers from a date range?

if a have a date range : 1/1/2017 - 2/1/2017, what Week Numbers those dates belong to??

]]>if a have a date range : 1/1/2017 - 2/1/2017, what Week Numbers those dates belong to??