not sure how to apply nested if to my problem

1. not sure how to apply nested if to my problem

hi,

ive got a spreadsheet, (attached), with 4 groups,
postcode, order type, day, branch.

im trying to create a pivot chart showing the number of orders over time, shown by a line graph, either by branch, or by order type, or both.

im struggling first of all to use the nested if function (if that is the correct function to use!) to get it to give a value with reference to another cell

for example, i have 5 order types,
Emergency = E
Specialist Inspection: Asbestos = F
Specialist Inspection: Roof = R
Specialist Inspection: Trace & Access = T
Works = W

I want it to show in 1 column 'E', or 'F', or 'T' etc.etc..... for each corresponding order type - i've tried using the nested if function but can't get it to work.

I then have the problem of trying to sum up, for each day of the week, total amount of orders per branch for each order type. I've absolutley no idea how to approach it.

I've attached another file, showing the table i'd like to end up with.

Would appreciate some help with this please!

2. ``Please Login or Register  to view this content.``
id rather use a look up put all those in a list
somwhere out of sight or create a data sheet to refer to so
it goes
BA .............................................................BB
order type
Emergency...................................................E
Specialist Inspection: Asbestos........................F
Specialist Inspection: Roof..............................R
Specialist Inspection: Trace & Access...............T
Works..........................................................W
then use a look up
=vlookup(c3,ba:bb,2,false)

3. Doh! of course VLOOKUP i completely forgot

thanks for the formula as well.

unfortunately after using your solution, i haven't really managed to put the data in a way that it can be used to create a pivot chart.
i really need an excel expert like yourself to tell me how you'd go about what im trying to do:

Ive attached the raw data file ive got. Each tab represents a 7 day week.
The data itself displays the number of orders, per day, for each type of postcode group (AL, CM etc..etc.).
Each postcode group belongs to 1 of 3 branches (i've listed on the last tab the postcode groups and their corresponding branches).

I want to show the number of orders (y axis) over time (x axis), with lines showing trend - i'd like 3 lines to show all the data split by branch, AND also to be able to show it by order type as well.

I just have no idea how to go about it - i thought i was going down the right path when i posed my question, but it was way too complicated, and i couldnt make a pivot table out of it (so i could make the pivot chart).

what would you guys do? im willing to even pay someone a tenner to come up with a solution to this

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1