Hello
I know this should be simple.
Set-up pivot table
Options > Calculations > Fields items sets > calculated field
Name the field
Select columns
Add
However
When I set up subtract finish from starts to give a total - it gives odd rubbish result
Start Finish Total
10 2 329263
There must be something simple I'm doing wrong?
Using excel 2010
HI SkinPup,
What formula you are using in calculated field..
can u post a small sample ? Thanks.
Regards,
DILIPandey
<click on below * if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
just
=start-finish
in the calculated field pop up box
I believe it should be =Finish-Start. Because subtraction should be done by Higher Value - Start Value
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
try finish minus Start and I believe this calculated field name is "Total" .. correct ?
Regards,
DILIPandey
<click on below * if this helps>
that just gives me the 329263 result but with a - sign in front.
Okay.. upload a sample file .
Regards,
DILIPandey
<click on below * if this helps>
Sorry the data is all pretty confidential, can't really post it, I hoped someone would spot a simple error that I was making...
okay... after applying the formula for calculated field, what you named the field as ?
Regards,
DILIPandey
<click on below * if this helps>
actual.
what i need to do is subtract the finished number form the start number. should be simple right?
What I was trying to figure out.. if / where you are using that calculated field as you have given following structure and there is no "actual"
Start Finish Total
10 2 329263
Regards,
DILIPandey
<click on below * if this helps>
whoops sorry yeah total
so now the situation is :-
Start Finish Total
10 2 329263
where total = finish - start
i.e., 2-10 = 329263which is mathematically not possible. This is interesting.... Can you copy paste as values just the two or three lines for me
Regards,
DILIPandey
<click on below * if this helps>
start end total trail.xls
Unfortunately i'm using mac excel 2011 at home, just to confuse things further!
But it still doesn't seem to do what it should...
Hi skinpup,
1) you were using "count of dates" instead of "sum of dates" - though sum will also not be correct because if a client / office has a same start date, pivot will add that.. but as per your data, it is OK to use sum as of now
2) After above point, you need to format the field as "date"
3) Formula should be "end - start" and you were using "start - end". It's like you are subtracting dec 20 - dec 25 and hence answer would be negative 5, So ideally you should subtract like dec 25- dec 20 = 5
4) where there is no end date i.e, cell is blank and pivot has calculated that as "Zero minus start date" i.e., [ 0- 1/31/2012 = -40939 ], where -40939 is actually 1/31/2012 in cell D6 and it is appearing like this because I have formatted Total field as General to give you difference in number of days
Note:- if you want, you can use conditional formatting to hide negative values (as mentioned in above point 4)
See the attached file and compare this with your file to understand better
start end total trail.xls
Regards,
DILIPandey
<click on below * if this helps>
Hello
Thanks for taking the time to have a look but that's not what I was hoping to achieve.
I need to know:
'count of clients who start- - 'count of clients who have ended' = 'total current count who have started'
Will the sheet not work this out for me?
Martin
Hi Martin,
so if two clients have end date that means your result would "total clients minus 2", So we can take "total of clients"-"count of clients having any end date" = "your result" ... correct?
use below formula in sheet F1 cell of sheet 1:-
=(COUNTA(B:B)-1)-(COUNTA(E:E)-1)
above will give you what you asked below :-
Not sure why you were after "calculated field" and my bad I was trying you to coach on "calculated filed" .. ha ha'count of clients who start- - 'count of clients who have ended' = 'total current count who have started'
Regards,
DILIPandey
<click on below * if this helps>
mmm i could have just added the formula but thought the calculated field would be easier, then when I couldn't get it to work was more determined than ever to use calculated field!
Can I get calculated field to do this, rather than mucking about with formulas etc?
okay.. then just use calculated field in data area and format that as "general" .. see the attached file
start end total trail.xls
Regards,
DILIPandey
<click on below * if this helps>
Hello
Just read page on excelgirl website calculated field don't work on count or average functions or sub totals!
Oh well formulas in the data table it is....
okay..
Did you checked the attachment I posted in Post #19? .. hope that helps lil bit
Regards,
DILIPandey
<click on below * if this helps>
I did dilipandey. Thanks for taking the time to try and help out.
Martin
You are welcome Martin...
Cheers
Regards,
DILIPandey
<click on below * if this helps>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks