Easy Calculation: PP How to?

JB3NOCO

New Member
Joined
Jan 10, 2017
Messages
1
I am a intermediate user of Excel and am in the process of learning more. I have a spreadsheet of sales data. Our sales is very easy. If the sales person works alone the get 100% of the deal, or 1 count. If two people work on a sales deal together they share the deal 50/50, or .5. I have two columns of sales people.

SALES PERSON 1SALES PERSON 2
GARRETTBRANDON
AMYRAY
RADLICKAMANDA
CHANCE
RAYRYAN
SLAYMAKER
RICHARD
TELLRADLICK
JAMIEMAX
SLAYMAKER
HARLIE
CHANCE

<colgroup><col><col></colgroup><tbody>
</tbody>

In this example:
Garrett - .5
Amy - .5
Radlick - .5+.5=1
Chance - 2
Ray - 1
Slaymaker - 2
Richard - 1

I am working with Pivot Tables ( if another way is easier, im all ears), how do i count the setup the PT to count the number of deals per Sales Person correctly?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I would use Power Query to load the data into a different 2 column table.

Sales Person. %

each row would be 1 name followed by either 50% or 100% depending if the name came from a row that had 1 Name or 2.

In Power Query, first add a new custom column with an if statement. Pseudo code: If sales person 2 is blank, then 100% else 50%
then append sales Person 2 below sales person 1 keeping the percentage column for both.

I hope that makes sense.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top