Excel formula using letters and numbers help please

DaZzZa

DaZzZa

DaZzZa

Soldato
Joined
2 Dec 2002
Posts
2,647
Location
Peterlee
hello,
im trying to make a spreadsheet for my dad to calculate the type of milk delivered and quantity so he can look at the database on a specific day and be able to place an order from his suppler for all the milk needed. so say column C is Mondays milk i want to put at the bottom a formula to add up milk needed and type of milk
so the values i need to use for different types will be
s/b = small blue
s/g = small green
b/b = big blue
b/g = big green
p = silver
S = red
b = blue
st = sterilized
o/j = orange juice
eg = eggs
each with a number infront to say how much 1s/b = 1 small blue
any help would be greatly appreciated as a formula like this is way over my head its using excel 2002
thanx very much
Daz
 
you can set up a table with the milk type and total it using countifa

eg
s/b 1
s/g 3

If you want to put the final result all in one cell as a long list, it's going to get a lot more complicated because you're into the realm of writing a custom function using if statements to total up each type into a variable then shove the whole lot out as a string...
 
Could you explain a bit more? I'm not entirely clear. Is it the case that you have a column for Monday, which may have multiple values for the same item (e.g., 1o/j and 2o/j, hence 3o/j is required)? If so, I'd consolidate using a pivottable. But first I'd rearrange the data a bit.

Have the columns as follows (make sure each of them has a heading):
A - Day of the week (Mon/Tues/Wed etc)
B - Type of item (e.g., s/b, S, o/j)
C - Number required for this line item

Then select columns A to C (try to make sure you select right to the bottom of the spreadsheet (line 65536 or so), as that may come in handy later), and go to the Data menu, and run the pivottable wizard. You should be able to OK through most of it, if not all.
Bung column A into the Page field, column B into the Row field, and C into the Data field. You may find that it counts the number of records for each type, rather than adding the quantities - in that case, double click where it says "Count of..." should be just above the top of the row field), and change it to summarise by "Sum".

Your dad can then open up the pivot table, change the day in the page field, and it will summarise the requirements for that selected day. If the raw data listing what's required changes, then right click on the pivot table and tell it to update. If you want to see it for multiple days all at once, then consider putting column A's data into the Column Area field of the pivot table.

Not sure if that answers your question, but is hopefully food for thought!
 
best way i can explain is this

thats with me manualy putting totals in
and ive got a feeling its gonna be complicated
 
With that layout, I'd raise it from custom to (bordering on) stupidly complicated, because you're storing multiple pieces of data in a single cell.

I've got to go out, but give me a couple of hours and I'll see what I can do for you to get a better layout, and therefore better (and much easier) calculations.

Are you using Excel 2003 or 2007?

Edit: Few questions. What are you trying to achieve? Excel isn't really the right system for order and stock management, but something could be knocked together to manage things on a week by week basis. Ideally, you want to be using some sort of database driven system for anything more complex or with deeper levels.
 
Last edited:
ok thanx for all the replys guys ive managedto put together a simple database thats has got the job done just need to tweak it so my dad can use it to input new customers as he isnt computer literate

 
You must log in or register to reply here.
Back
Top Bottom