CHERTSEY

BOATS, BRIDGES, BOILERS ... IF IT'S GOT RIVETS, I'M RIVETTED
... feminist, atheist, autistic academic and historic narrowboater ...
Likes snooker, beer, tea, rivets and solitude, and is strangely fascinated by the cinema organ.
And there might be something about railways.
**********************************************************************************

Tuesday, 4 July 2017

Victualling

I have never really got to grips with spreadsheets. It seemed to me that they got used - at work - all too often when a simple table would have served instead. Over the past few years I've used them more and more, and learned how to do simple things with them, on a strictly need-to-know basis.

A few months ago, however, I realised that these new found (and hard won) skills could be pressed into the service of boating - and greated the Great Chertsey Victualling Spreadsheet. Basically, I input the number of days we're going away for, and the inventory of what's already on board, and it creates a shopping list.up to the nearest whole number - rounding down is no use when I need to know how many packs to buy. Is this truly impossible in Excel?
A refinement which no one has yet been able to tell me how to make (I've been suggested some workarounds) is to get the final shopping list column to always round

I started with estimates of how much we use of each item daily, which can be adjusted with more experience. Meals can be planned in this way because of the cunning way we eat whilst boating - which I will spin out into another post.
Posted by at

7 comments:

  1. Jemma4 July 2017 at 22:39

    Have you tried the ROUNDUP function? For example in row 10 if you enter =ROUNDUP((D10-F10),0) in cell G10, this will return 1, rather zero.

    ReplyDelete
  2. KevinTOO4 July 2017 at 22:41

    I can't fault your spreadsheet... it even correctly states that Marmite should be -1 (or thrown overboard...LOL)

    ReplyDelete
  3. Davidss5 July 2017 at 08:31

    This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Davidss5 July 2017 at 08:42

      Jemma's suggestion is fine (although I've never used the ROUNDUP function).
      If I want a round total my action is to right click the appropriate cell*, select Format Cells, Number, and set the Decimal places to 0.
      In this case I'd right click the column header (G), which would apply the re-formatting to all the cells in that Column.

      Apologies for the clumsy edit (Delete original, then post the updated version).

      Regards.

      Delete
    2. Sarah5 July 2017 at 18:53

      The trouble with that is that if the odd is less than .5, it will round down, which is just what I don't want. Even the 'round up' function in Excel seems to do this.

      Delete
  4. Herbie Neil5 July 2017 at 11:33

    Enjoying imagining you measuring out 2.5 grams of Marmite for your toast!

    ReplyDelete
  5. Davidss6 July 2017 at 08:36

    Your point is, of course valid (less than 0.5 and the cell value is rounded down). To show my 'working out' (as they said in maths exams), I created a Search term for Google "if excel value is greater than 0 always round up". One of the results was "IF Greater than 0 but less than .5 rounding up to 1", which seemed appropriate (http://tinyurl.com/y7gec3rg).
    The solution credited by the OP with working combines the IF and ROUNDUP functions "=IF(A1*B1<1,ROUNDUP(A1*B1,0),A1*B1)".
    Obviously some adaptation is required for your spreadsheet, but the principle is there.
    Regards.

    ReplyDelete

Subscribe to: Post Comments (Atom)