Hi all

Hoping someone can help me with this, I want to add an image to a donut chart when it reaches a certain percentage.

Some context, I have a donut chart (pivot table generated) that shows data compliance and once it reaches 100% I want to overlay it with an image of a real donut, just for a bit of fun for my team.

Any idea on how I can do this?

Thanks

]]>Hoping someone can help me with this, I want to add an image to a donut chart when it reaches a certain percentage.

Some context, I have a donut chart (pivot table generated) that shows data compliance and once it reaches 100% I want to overlay it with an image of a real donut, just for a bit of fun for my team.

Any idea on how I can do this?

Thanks

I've been an Excel user for 20 years. Pivot tables aren't my specialty but I use them quite often. I've run into an issue where my pivot table totals (grouped by week) were not matching the totals coming from my ERP system. When I drilled down on the pivot table total, I noticed the data detail doesn't match the master data extracted out of the ERP system into the table supplying the pivot table.

I'm running Office 2013 - 64 bit.

By chance is this a "known" issue under certain circumstances?

I have 14 data records. 13 records match (spreadsheet matches pivot table detail). 1 record doesn't match. Record at issue is below. The last field (-10.78) is included in the pivot table sum. The detail lists it as -5.61. (There is no data in the master data with a value of -5.61.) My pivot table sum is off by the difference.

Data from spreadsheet

000 3/11/2017 BC Itm Est 0000015761 N * 02 00317031 7009908 16501 -10.78

Data from pivot table detail

000 3/11/2017 BC Itm Est 0000015875 N * 02 00317031 7009923 3555 -5.61

Any ideas on how to address this? It's happening with numerous data records. This is just one example.

My first thought - sorting the data into weekly buckets by using a grouping is somehow causing the issue.

]]>I'm running Office 2013 - 64 bit.

By chance is this a "known" issue under certain circumstances?

I have 14 data records. 13 records match (spreadsheet matches pivot table detail). 1 record doesn't match. Record at issue is below. The last field (-10.78) is included in the pivot table sum. The detail lists it as -5.61. (There is no data in the master data with a value of -5.61.) My pivot table sum is off by the difference.

Data from spreadsheet

000 3/11/2017 BC Itm Est 0000015761 N * 02 00317031 7009908 16501 -10.78

Data from pivot table detail

000 3/11/2017 BC Itm Est 0000015875 N * 02 00317031 7009923 3555 -5.61

Any ideas on how to address this? It's happening with numerous data records. This is just one example.

My first thought - sorting the data into weekly buckets by using a grouping is somehow causing the issue.

hi,

someone just came up with the a good test, seems simple, but...

She wants a checklist of tasks, that is structured where the person responsible can easily click a checkbox to validate they have done the task.

Structured as in the same place, she wants to filter for a client, and have different sections regroup tasks pertaining to that section.

This check list will evolve

So first step, structure your data, put in a basic list, translate to a pivot with sections and tasks as dimensions, clients as filters.

Next: the checkbox. i can easily make a checkbox that will write down a username into a specified cell, but I can't seem to integrate that so the check box knows in front of which task it finds itself in the pivot table.

Does anyone want to try and suggest something?

]]>someone just came up with the a good test, seems simple, but...

She wants a checklist of tasks, that is structured where the person responsible can easily click a checkbox to validate they have done the task.

Structured as in the same place, she wants to filter for a client, and have different sections regroup tasks pertaining to that section.

This check list will evolve

So first step, structure your data, put in a basic list, translate to a pivot with sections and tasks as dimensions, clients as filters.

Next: the checkbox. i can easily make a checkbox that will write down a username into a specified cell, but I can't seem to integrate that so the check box knows in front of which task it finds itself in the pivot table.

Does anyone want to try and suggest something?

Hi All,

I recently acquired power pivot to analyse large amount of data generated in field trials conducted for registration of crop chemicals. The plan is to build foundation for dynamic reporting of various key stages of the process.

I managed to organise data in several data and lookup tables, which I then uploaded to powerpivot data model. However, when I create the various pivot reports, rows for certain fields are replicated. It seems the relationships are not setup correctly but I cannot figure out exactly what the problem is.

I uploaded a sample dize document of a couple of projects called 'master data', and 'reports' which are suppose to be linked. Is it possible for someone with experience in creating dashboards and such with power pivot could advise?

Much appreciated

I recently acquired power pivot to analyse large amount of data generated in field trials conducted for registration of crop chemicals. The plan is to build foundation for dynamic reporting of various key stages of the process.

I managed to organise data in several data and lookup tables, which I then uploaded to powerpivot data model. However, when I create the various pivot reports, rows for certain fields are replicated. It seems the relationships are not setup correctly but I cannot figure out exactly what the problem is.

I uploaded a sample dize document of a couple of projects called 'master data', and 'reports' which are suppose to be linked. Is it possible for someone with experience in creating dashboards and such with power pivot could advise?

Much appreciated

I have 120 students. They need to be placed into 8 groups. Those groups need to be created based on their gender and their preference for whom they would like to be with ie Jane was asked and would prefer to be in a group with 1st Edith J, 2nd Fiona, 3rd Jenny M. How can I do this using Excel?

]]>Hi Everyone

I m trying to add field to my powerpivot that compare figure with last record for the same customer

can you help please ?

Thanks

I m trying to add field to my powerpivot that compare figure with last record for the same customer

can you help please ?

Thanks

Hi,

i am new to power query and power pivot, i am using excel 2016 and got the following error when trying to combine two tables in query from table in data tab.

"we couldn't get data from the data model, here is the error message we got; No error message available, result code : -2146233052(0x80131524)".

I tried a lot but not found any power table add-in in excel 2016. there is only one com Add-ins available that is Microsoft Power Map for excel.

Power pivot is working but Calculated fields are not working and become grayed.

Please help

]]>i am new to power query and power pivot, i am using excel 2016 and got the following error when trying to combine two tables in query from table in data tab.

"we couldn't get data from the data model, here is the error message we got; No error message available, result code : -2146233052(0x80131524)".

I tried a lot but not found any power table add-in in excel 2016. there is only one com Add-ins available that is Microsoft Power Map for excel.

Power pivot is working but Calculated fields are not working and become grayed.

Please help

Hi,

I hope this question has not been posted before.

I would like to plot a table using three data ranges that I already have on my spreadsheet.

I have 3 temperature data sets; 2 sets from upstream and one set from downstream.

I would like to plot three different lines on my graph:

-the downstream temp.

-the mean of the two upstream temps + the standard deviation time 3.

-the mean of the two upstream temps minus the standard deviation time 3.

My problem is that I want to do this without having to pre do the calcs on my spread sheet. I want to keep it all very neat on still only have the three data sets on display.

Could I do this in a macro?

the point of this would be to be able to visually see when the downstream temp fall out of the given boundary.

I hope I have explained this clearly.

thanks in advance for any help or advice.

]]>I hope this question has not been posted before.

I would like to plot a table using three data ranges that I already have on my spreadsheet.

I have 3 temperature data sets; 2 sets from upstream and one set from downstream.

I would like to plot three different lines on my graph:

-the downstream temp.

-the mean of the two upstream temps + the standard deviation time 3.

-the mean of the two upstream temps minus the standard deviation time 3.

My problem is that I want to do this without having to pre do the calcs on my spread sheet. I want to keep it all very neat on still only have the three data sets on display.

Could I do this in a macro?

the point of this would be to be able to visually see when the downstream temp fall out of the given boundary.

I hope I have explained this clearly.

thanks in advance for any help or advice.

I have two problems I'm trying to solve with the same table.

The goal is to create a 3D pie chart showing the numbers of each unique type of unit.

Problem #1, in the table (shown below) I have both efficiency units and bed/bath listings. I have been able to extract the unique values of beds and baths but this method does not work for the term "Efficiency" since I'm triggering off of the backslash.

Problem #2 is that I can create the unique values, i.e, "1 Bed", "2 Bed", etc., in the table but I cannot figure out how to chart the numbers of units that match each unique value.

The formulas used currently are:

C2: "=LEFT(A2,FIND("/",A2)-1)"

D2: "=RIGHT(A2,LEN(A2)-FIND("/",A2))"

E2: "=IFERROR(IF(COUNTIF(C$2:C2,C2)=1,C2,""),"")"

I've also attached an example worksheet.

The goal is to create a 3D pie chart showing the numbers of each unique type of unit.

Problem #1, in the table (shown below) I have both efficiency units and bed/bath listings. I have been able to extract the unique values of beds and baths but this method does not work for the term "Efficiency" since I'm triggering off of the backslash.

Problem #2 is that I can create the unique values, i.e, "1 Bed", "2 Bed", etc., in the table but I cannot figure out how to chart the numbers of units that match each unique value.

The formulas used currently are:

C2: "=LEFT(A2,FIND("/",A2)-1)"

D2: "=RIGHT(A2,LEN(A2)-FIND("/",A2))"

E2: "=IFERROR(IF(COUNTIF(C$2:C2,C2)=1,C2,""),"")"

I've also attached an example worksheet.

Code:

__Unit Type(A) Units(B) Beds(C) Baths(D) Unique Beds(E) Counts(F)__

Efficiency 6 #VALUE! #VALUE!

1 Bed / 1 Bath 2 1 Bed 1 Bath 1 Bed

1 Bed / 1 Bath 70 1 Bed 1 Bath

2 Bed / 1.5 Bath 93 2 Bed 1.5 Bath 2 Bed

3 Bed / 2 Bath 94 3 Bed 2 Bath 3 Bed

3 Bed / 2 Bath 78 3 Bed 2 Bath

3 Bed / 2 Bath 62 3 Bed 2 Bath

Hello, I'm new here and I really need HELP. Im sorry for my english(not a native speaker) But I really hope you can help me...

I'm in a new job and boss wants a report from me, but I don't know how to do it. Im pretty decent with Excel, even really good with VBA but Pivot tables are hell for me.

I have a sheet of date with ratings of sales reps. Columns are Date,Sale_Rep_Name,Num_of_questions,Rating,Change,Rating_After_change. Every row is a rating of a Sales rep by customer, num of questions says how many questins did the cust. answer (max 4 questions). Rating is the rating given by cust. Change says if there was a manual change of rating by my boss (could be becouse the cust. didnt really understand the questionnaire) if there was something bad with the question(change = 1) then rating is changed to 1.

What I need to show on my report is connection between changes and number of questions. For example, would there be a less manual changes if we counted only ratings where cust. answered all four questions?

How to do this?

I'm in a new job and boss wants a report from me, but I don't know how to do it. Im pretty decent with Excel, even really good with VBA but Pivot tables are hell for me.

I have a sheet of date with ratings of sales reps. Columns are Date,Sale_Rep_Name,Num_of_questions,Rating,Change,Rating_After_change. Every row is a rating of a Sales rep by customer, num of questions says how many questins did the cust. answer (max 4 questions). Rating is the rating given by cust. Change says if there was a manual change of rating by my boss (could be becouse the cust. didnt really understand the questionnaire) if there was something bad with the question(change = 1) then rating is changed to 1.

What I need to show on my report is connection between changes and number of questions. For example, would there be a less manual changes if we counted only ratings where cust. answered all four questions?

How to do this?

Hello,

I have an Excel document containing two sheets; on the first sheet, input data are located, on the second sheet, I do perform calculations with the input data.

Input data: A list of locations which several variables attached, e.g. temperature and altitude,__sorted alphabetically__.

Calculation: A location's variable is multiplied with a certain factor (individual per location).

Thus, what I have is basically a table which is split between page 1 and 2; some location attributes (temperature, altitude) are on page 1, others (multiplication factor and result) are on page 2.

Now to my problem: The input data table is flexible, meaning that new locations can be added; the location list however stays sorted alphabetically. How can I make sure that the calculation factors on the second page stay attached to the right locations also if a new location is added "in the middle" of the location list?

An example: On page 1, I have the locations London and Paris, and each city has got an altitude value.

On page 2, I reference this table via "=A1", "=A2" etc, and I add an extra column for the multiplication factor and the result:

If I add an extra location "Moscow", alphabetically between London and Paris on page 1, the Paris multiplication factor is wrongly attached to it on the second page. How can I make sure the Paris multiplication factor stays attached to the location Paris on page two?

Thanks already in advance :)

Requin

]]>I have an Excel document containing two sheets; on the first sheet, input data are located, on the second sheet, I do perform calculations with the input data.

Input data: A list of locations which several variables attached, e.g. temperature and altitude,

Calculation: A location's variable is multiplied with a certain factor (individual per location).

Thus, what I have is basically a table which is split between page 1 and 2; some location attributes (temperature, altitude) are on page 1, others (multiplication factor and result) are on page 2.

Now to my problem: The input data table is flexible, meaning that new locations can be added; the location list however stays sorted alphabetically. How can I make sure that the calculation factors on the second page stay attached to the right locations also if a new location is added "in the middle" of the location list?

An example: On page 1, I have the locations London and Paris, and each city has got an altitude value.

Location |
Altitude |

London | 10 |

Paris | 200 |

On page 2, I reference this table via "=A1", "=A2" etc, and I add an extra column for the multiplication factor and the result:

Location |
Altitude |
Factor |
Result |

London | 10 | 5 | 50 |

Paris | 200 | 10 | 2000 |

If I add an extra location "Moscow", alphabetically between London and Paris on page 1, the Paris multiplication factor is wrongly attached to it on the second page. How can I make sure the Paris multiplication factor stays attached to the location Paris on page two?

Thanks already in advance :)

Requin

Hi everyone,

I'm not sure if that's the right place in the forum for this question, but I will give it a try.

I have 3 sets of data that I need to plot. Using Box and Whisker plot I am getting this:

box1.PNG

I need the 3 sets to be positioned on x axis not arbitrary. So there should be a bigger gap between 4 and 5.

Is there a way of doing this?

Many thanks for your comments.

]]>I'm not sure if that's the right place in the forum for this question, but I will give it a try.

I have 3 sets of data that I need to plot. Using Box and Whisker plot I am getting this:

box1.PNG

I need the 3 sets to be positioned on x axis not arbitrary. So there should be a bigger gap between 4 and 5.

Is there a way of doing this?

Many thanks for your comments.

I can not seem to calculate the percentage of a total using my value field settings. Any thoughts? I know I can adjust my data table, but in this case I want to keep it all within the Pivot. I want to calculate the percent fall on/above. so fall on/above/count of student#pivot.JPG

]]>Hello,

I have a workbook (Excel 2013) with multiple worksheets. Several of the worksheets have charts that are linked to a PowerPoint 2013 file. So, I have about ten slides in PowerPoint that host Excel charts via Object linking. Each chart is on a separate worksheet in the same workbook.

Anyway, when I select "Update Links" upon opening the file, it updates to the worksheet that holds the data instead of the chart on the chart's worksheet. Does anyone know what I'm doing wrong?

John

]]>I have a workbook (Excel 2013) with multiple worksheets. Several of the worksheets have charts that are linked to a PowerPoint 2013 file. So, I have about ten slides in PowerPoint that host Excel charts via Object linking. Each chart is on a separate worksheet in the same workbook.

Anyway, when I select "Update Links" upon opening the file, it updates to the worksheet that holds the data instead of the chart on the chart's worksheet. Does anyone know what I'm doing wrong?

John

Hi Guys,

I am trying to build a cash forecasting model for payments to suppliers.

I am basically extracting aged creditors with various currencies as my data and need to be able to quickly show values payable by currency as at certain dates. I have used pivot to display information as required and in the filter option on pivot wizard I have selected due date to be able to quickly filter between whatever dates are required (i.e. my management team ask for). I would then hope to filter on this to be able to quickly filter by year, months and days to show amount payable in each currency upto that due date (as picked in due date filter on pivot)

My problem is the filter function is not displaying dates in the pivot as it does in the data sheet (see screenshots to try and explain)

Data Filter

Data Filter.PNG

Pivot Filter

Pivot Filter.PNG

How can I get it so I can expand on year, months and days on pivot filter as it would on filter function on the data sheet?

Any help would be greatly appreciated.

]]>I am trying to build a cash forecasting model for payments to suppliers.

I am basically extracting aged creditors with various currencies as my data and need to be able to quickly show values payable by currency as at certain dates. I have used pivot to display information as required and in the filter option on pivot wizard I have selected due date to be able to quickly filter between whatever dates are required (i.e. my management team ask for). I would then hope to filter on this to be able to quickly filter by year, months and days to show amount payable in each currency upto that due date (as picked in due date filter on pivot)

My problem is the filter function is not displaying dates in the pivot as it does in the data sheet (see screenshots to try and explain)

Data Filter

Data Filter.PNG

Pivot Filter

Pivot Filter.PNG

How can I get it so I can expand on year, months and days on pivot filter as it would on filter function on the data sheet?

Any help would be greatly appreciated.