Hi,

I have attached a sample of sales data in columns A:E and would like to create a formula in cell I2 that will calculate the weighted average growth rate for whichever Product is named in cell H2.

The weighted average can usually be done simply with a SUMPRODUCT function:

=SUMPRODUCT(growth rates for AAA, weights for AAA/total weight of AAA)

I'm struggling to get the formula to accommodate both the condition (to return the value for product AAA) and also to deal with the n/a text in the weight and growth columns.

The expected answer for AAA is hard-pasted in cell I2.

Can someone please suggest a SUMPRODUCT solution.

Thanks!

I have attached a sample of sales data in columns A:E and would like to create a formula in cell I2 that will calculate the weighted average growth rate for whichever Product is named in cell H2.

The weighted average can usually be done simply with a SUMPRODUCT function:

=SUMPRODUCT(growth rates for AAA, weights for AAA/total weight of AAA)

I'm struggling to get the formula to accommodate both the condition (to return the value for product AAA) and also to deal with the n/a text in the weight and growth columns.

The expected answer for AAA is hard-pasted in cell I2.

Can someone please suggest a SUMPRODUCT solution.

Thanks!

The timevalue formula is not working for some of my time values. If the time is showing as 2159 it converts to 21:59.

If the time is showing as 59 or 1 it does not covert.

I am using this formula: =TIMEVALUE(LEFT(E2,LEN(E2)-2)&":"&RIGHT(E2,2))

thanks

]]>If the time is showing as 59 or 1 it does not covert.

I am using this formula: =TIMEVALUE(LEFT(E2,LEN(E2)-2)&":"&RIGHT(E2,2))

thanks

for some reason rows 2 through 1416 have disappeared from my worksheet. they are not hidden but rather they have been collapsed. I can expand them one by one but when I do the numbers are blue and it will take forever to expand them all. How did this happen and more importantly how to I get them back?

]]>please correct me with this formula.....

IF(AND(Sheet1!L2=B1,Sheet1!G2=A2),(Sheet1!E2),""),IFERROR(INDEX(Sheet1!$E2:$E$1000,MATCH('2016'!$A2,Sheet1!$G$2:$G$1000,0)),"")

t1.PNG

]]>IF(AND(Sheet1!L2=B1,Sheet1!G2=A2),(Sheet1!E2),""),IFERROR(INDEX(Sheet1!$E2:$E$1000,MATCH('2016'!$A2,Sheet1!$G$2:$G$1000,0)),"")

t1.PNG

This post is similar to one I posted last week but I cannot seem to see why the statement does not work.

I am trying to add the value in field "total by customer" to the end of the address data which is the records in the first of the excel file. I am matching on customer # and customer id.

Thank You.

I am trying to add the value in field "total by customer" to the end of the address data which is the records in the first of the excel file. I am matching on customer # and customer id.

Thank You.

Hi,

Still in its infant stages... Not sure it will work because it looks pretty tiny on my 17" laptop so the final test will be on a 22" inch at work but...

Using a SUMIF formula but can't figure out how to filter the results to the corresponding year in col A.

Help would be appreciated.

And suggestions, advise or opinions are also most welcome.

Just trying to do something different to avoid spreadsheets with a zillion rows to scroll through.

Still in its infant stages... Not sure it will work because it looks pretty tiny on my 17" laptop so the final test will be on a 22" inch at work but...

Using a SUMIF formula but can't figure out how to filter the results to the corresponding year in col A.

Help would be appreciated.

And suggestions, advise or opinions are also most welcome.

Just trying to do something different to avoid spreadsheets with a zillion rows to scroll through.

Is this possible ?

Store LEN(A2) to V1, like LEN(A2)=V1 then = MID(A2,V1,V1-1) ?

]]>Store LEN(A2) to V1, like LEN(A2)=V1 then = MID(A2,V1,V1-1) ?

Hello. I'm looking for a formula to enter values in a column that are the smallest value of a set of duplicates. Example:

So, in the above table the formula would be in the 'Smallest' column and its entries would be:

The formula I arrived at by searching forums and Google can be seen in the 'dupes' column in the attached excel workbook. Unfortunately, it's not working as I had hoped, it's just pulling the first duplicate's quantity rather than the lowest duplicate's quantity.

Beggars can't be choosers and all that but I don't know anything about macros and VBA so I'd greatly prefer a formula that I can learn, understand, modify and apply when I run into a similar situation in the future.

Any help would be greatly appreciated. Thanks in advance.

Item | Quantity | Smallest |

Screw 1 | 2 | |

Screw 2 | 4 | |

Screw 3 | 2 | |

Screw 1 | 1 | |

Screw 3 | 8 |

So, in the above table the formula would be in the 'Smallest' column and its entries would be:

Smallest |

1 |

'blank' |

2 |

1 |

2 |

The formula I arrived at by searching forums and Google can be seen in the 'dupes' column in the attached excel workbook. Unfortunately, it's not working as I had hoped, it's just pulling the first duplicate's quantity rather than the lowest duplicate's quantity.

Beggars can't be choosers and all that but I don't know anything about macros and VBA so I'd greatly prefer a formula that I can learn, understand, modify and apply when I run into a similar situation in the future.

Any help would be greatly appreciated. Thanks in advance.

Hi Guys,

Last time I got issue solved for AHT (Average Handling time) ranging , in the same line now I want to know if we can calculate the AHT for that particular day for different individual and on the other column MTd AHT

Please find the attachment and the section highlighted in yellow to calculate AHT for the day for the individual and calculate MTD in the same way.

Last time I got issue solved for AHT (Average Handling time) ranging , in the same line now I want to know if we can calculate the AHT for that particular day for different individual and on the other column MTd AHT

Please find the attachment and the section highlighted in yellow to calculate AHT for the day for the individual and calculate MTD in the same way.

In Column A, I have a Formula which extract Strings like, "I am going to school" from another column

When : Data to Columns (data from Column A), I get the Formula to split not the "value" in column A

If I copy the Value of column A, to column B, I can split it but I am loosing the relation with column A

]]>When : Data to Columns (data from Column A), I get the Formula to split not the "value" in column A

If I copy the Value of column A, to column B, I can split it but I am loosing the relation with column A

Hello,

I want excel paint a specific number in all my file (with multiple sheets)

For example: For number**555** I want blue cell, so for all cells who had the number 555

It is possible? How can i do it?

Thanks

]]>I want excel paint a specific number in all my file (with multiple sheets)

For example: For number

It is possible? How can i do it?

Thanks

Hello everyone!

I'm currrently doing my master's dissertation (in Finance) and would like to ask you for some help regarding the shaping of the data I've collected. I'm doing a panel data analysis and therefore have data for several companies across time.

Attached goes a sample file so you can better visualise how I've collected the data and how i want to reshape it. The green tabs are some of the variables I'll use in my analysis, with each tab having info on the respective variable for several companies across several years.

I need to take this data and reshape it into panel data form, as in the first tab (variables in columns and companies and years in lines). I'm looking for a way (either through formulas, macros,...) to do that relatively fast, given that doing it by hand (copying and pasting with transpose, which is the first idea I came up with) is simply not feasible time wise.

I'd really appreciate any help you could give me, since it'll probably save me a lot of time and trouble.

Thank you!

I'm currrently doing my master's dissertation (in Finance) and would like to ask you for some help regarding the shaping of the data I've collected. I'm doing a panel data analysis and therefore have data for several companies across time.

Attached goes a sample file so you can better visualise how I've collected the data and how i want to reshape it. The green tabs are some of the variables I'll use in my analysis, with each tab having info on the respective variable for several companies across several years.

I need to take this data and reshape it into panel data form, as in the first tab (variables in columns and companies and years in lines). I'm looking for a way (either through formulas, macros,...) to do that relatively fast, given that doing it by hand (copying and pasting with transpose, which is the first idea I came up with) is simply not feasible time wise.

I'd really appreciate any help you could give me, since it'll probably save me a lot of time and trouble.

Thank you!

Hello,

I have finished my excel project. But I notice with all this VBA code running behind the scenes causes a lot of

flickering on the screen when executed.

Is there a way to minimize the flickering?

Thanks

RN

]]>I have finished my excel project. But I notice with all this VBA code running behind the scenes causes a lot of

flickering on the screen when executed.

Is there a way to minimize the flickering?

Thanks

RN

I've spent a week trying to figure out why "PivotTable Fields" sub-windows was not showing anything.

The problem turned out to be the display of the "PivotTable Fields" itself.

If you get this problem, right click on the Pivot Table in question and then on the "PivotTable Fields" window that opens, choose "Choose Fields to add to Report" box and then select on one of its options.

I usually use the top one: "Fields Section and Areas Section stacked".

Mac

]]>The problem turned out to be the display of the "PivotTable Fields" itself.

If you get this problem, right click on the Pivot Table in question and then on the "PivotTable Fields" window that opens, choose "Choose Fields to add to Report" box and then select on one of its options.

I usually use the top one: "Fields Section and Areas Section stacked".

Mac

Assuming I have a pivot table showing sales of 5 salesman during quarter 1, is there any way I can create two identical pivot tables with percentages ?

For example, below the pivot table I have another table (calculated manually) which shows that salesman 4, for example, was responsible for 25% of February sales (he sold 65K in February over a total sales of the company of 263K).

On the right of the pivot table I have another table (also calculated manually), which shows, that salesman 1 had 174K sales in Q1 and that 37% of those were in January.

My question is if I can, somehow, have a pivot table calculating these % automatically.

Many thanks

Sales.JPG

]]>For example, below the pivot table I have another table (calculated manually) which shows that salesman 4, for example, was responsible for 25% of February sales (he sold 65K in February over a total sales of the company of 263K).

On the right of the pivot table I have another table (also calculated manually), which shows, that salesman 1 had 174K sales in Q1 and that 37% of those were in January.

My question is if I can, somehow, have a pivot table calculating these % automatically.

Many thanks

Sales.JPG