Last year I managed to get the list of people and birds to generate the order sheet which the turkey supplier needed (I.e. counts by size). Quite happy with that. It reduced the opportunity for human error and missed turkeys. But it wasn't all that useful when the customer placed their order. Too many details needed to be input. Free-range bronze, organic or white turkey. Breast roast, crown or whole bird. Small, medium, large or 4, 5, 6 kg etc etc. And once I had all that I had to look up the price per kg of the appropriate combination on a list blue tacked to the wall! Then multiply it by the size. etc etc. This year I wanted my spreadsheet to pull its weight and do a greater share of the work.
And so begun my exploration of the nested if. And they aren't all that scary. I may need to talk out loud when writing them. But I can do it. So now when you order your Christmas turkey from Tastes*, I merrily enter the type you would like, the starting size, be that in kg or small/medium/large. And that is it. My lovely spreadsheet tells me the average size of the bird, the price per kg, the estimated price for your bird, the minimum and maximum size and the approximate balance once the deposit is paid. I'm pretty pleased with myself. And if anyone else wants a nested if, below is my largest combined if statement. But be aware, being an impoverished independent retailer I don't use Miscrosoft Excel, this is OpenOffice Calc and I think the semi-colons might need to be commas in Excel:
=IF(G17=$Summary.A$6;$Summary.C$6;IF(G17=$Summary.A$5;$Summary.C$5;IF(G17=$Summary.A$4;$Summary.C$4;IF(G17=$Summary.A$10;$Summary.C$10;IF(G17=$Summary.A$9;$Summary.C$9;IF(G17=$Summary.A$8;$Summary.C$8;IF(G17=$Summary.A$7;$Summary.C$7;"Error")))))))
Please don't write and tell me I could have done this much more easily!
Please do come and order turkeys and put my spreadsheet to the test!
* Tiny bit of subliminal marketing there.
No comments:
Post a Comment