Hi,
I have table with upper and lower limits defined as
F16&"<"&"Sales"&"<"&G16 so; 5 < Sales <= 10 etc.
But when I used this in a pivot it didn't sort correctly , so I added a "" to the start of each cell
""&F16&"<"&"Sales"&"<="&""&G16
and then it worked ok, but not entirely sure why, I've attached the workbook with both examples, have I moved the 5 from the 'tens' to "units' ?
Richard.
"F16&"<"&"Sales"&"<"&G16 so; 5 < Sales <= 10 etc."
F16 contains a value.
If Excel sorts a value it sorts 20 before 5, since 20 begins with 2.
After you add "" in the formula Excel sorts on text.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Well yes, that was my initial thought, but if you concatenate a number with text, doesn't it all become text anyway,
joining a and 5 to make a5 or 5a would be to the left ( text) , even before adding "", its not really a value its text already?
Or am I wrong?
Yes, you are right.
But you did not add a before 5 (a5 => which is text).
You just used 5 (5 => which is a value).
The formual is
"F16&"<"&"Sales"&"<"&G16 so; 5 < Sales <= 10 etc.",
were you just looking at the result of the formula and not the formula or actual tables?
so F16 (5) , will now become text, as is joined , but still won't sort correctly,
it is only after adding "", which I is a null value, so not blank, that the pivot sorts.
RD
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks