Posts by KiwiSteve

  • Colouring Part of a Chart Series

    Re: Colouring Part of a Chart Series


    Hi Andy
    Thanks for the suggestion. I tried another series, but it didn't align with the correct dates, ie on the right. It appeared on the left as if it was a short series starting on the first date, not ending on the last. The YValues were correct, but the XValues were not. When that didn't work I altered all the formulae to show the predicted values if date is greater than 'today'. That kept it al in one range.
    I'll keep thinking...
    Cheers
    KiwiSteve

  • Colouring Part of a Chart Series

    Hello All


    Apologies for such a detailed question - hopefully save lots of follow-ups!


    I have a chart that shows milk collection volumes throughout the year.
    The data across any region shows a see-saw pattern as day/night milkings differ, and many farms are only collected every second day.


    The purpose of the tool I'm creating for the Transport Manager is to more accurately predict milk volumes for the next few days to optimise tanker allocation to regions.


    He selects the first and last date for display, as well as a prediction for up to 7 days ahead. The prediction is based on a variable number of days 'behind', ie. depending on the shape of the recent volumes. He can also select from 10-30 days data to use for the predictions. They can choose from a linear, quadratic and cubic models which improves predictive accuracy.


    Because of the see-saw nature of the data, I have separated it into 'odd' and 'even' dates, and I plot two separate series. This allows the predictions to be for the separate trends in both higher and lower values.


    So far, all this works fine.


    What I'd like to do is to automatically colour the markers on both lines for just the predictions, ie from 'today' to the end of the series. When a new date is selected, it all updates for them.


    Depending on the selections there can be up to 200 dates/volumes displayed, but typically 30-ish, with up to 10 more as predictions - the last ones in the series.


    How do I select and modify points based either on their X-value, ie date, or based on the point number? Tried lots of variations, but just can't crack this one.


    Thanks in advance for your help.


    KiwiSteve

  • Solving for Unknown Variable

    Re: Solving for Unknown Variable


    To do this, set up a cell that finds the difference between Rate1 and Rate2.
    Select this cell, then Tools>Solver, which will open with your cell selected.
    That is the target cell which you want to optimise to 0, by changing the cell linked to the counter. (This, in effect, alters the counter until the two rates are equal, and is not an integer in the simple model you gave.)
    If it can find a solution it will tell you: press Accept Solution.


    Cheers


    Stephen

  • Tricky Formula

    Re: Tricky Formula


    Hi


    You don't say whether you can't get an answer, or whether the answer you get is wrong?


    I got this formula to work ok - it produces answers, but did notice that the ranges in the lookups aren't all the same: some have INDEX!$A$4:$E$729, while some have INDEX!$A$5:$E$729. Have you included headings in one and not the other, perhaps?


    If the item from cell A24 you are looking up is in the first row of the range on INDEX, then it wont find it.


    Check that and see if it works.


    Here's what worked for me:
    =IF(L15<4,
    IF(B24>0,($H$17*1.564)*(VLOOKUP(A24,INDEX!$A$5:$E$729,3,FALSE))*(VLOOKUP(A24,INDEX!$A$4:$E$729,4,FALSE))
    +(VLOOKUP(A24,INDEX!$A$5:$E$729,5,FALSE)),""),
    IF(B24>0,($H$17*1.564)*(VLOOKUP(A24,INDEX!$A$5:$E$729,3,FALSE))*(VLOOKUP(A24,INDEX!$A$4:$E$729,7,FALSE))
    +(VLOOKUP(A24,INDEX!$A$5:$E$729,5,FALSE)),""))


    Also, you have two ways of getting "". Does this matter?


    Cheers


    Stephen

  • Significant Figures Function

    Re: Significant Figures


    Steve Aprahamian suggested using =TEXT(....,"0.000"), etc to get it looking right, then using =VALUE(cell...) to handle it as a number. Sounds ok, so long as we can make the correct no. of zeros appear in the TEXT formula. Make sense?
    KiwiSteve

  • Significant Figures Function

    Re: Significant Figures Function


    Hi Jon


    It seems this doesn't work with some numbers. eg show 1 to 2sf should display 1.0, but shows 1 instead. Only get 1.0 if you format the no. of dps, which then prevents the correct sf appearing.
    I didn't test this, but Steve Aprahamian did, and, as per usual, he got the finer detail that lesser mortals like me missed! Apart from that shortcoming, it works really well.
    Cheers
    Stephen

  • Forum improvement

    Re: Forum improvement


    Hi Andy
    Thanks for this info. Shall be a bit easier from now on.
    Often don't have a lot of time to spend on the site, so I'm not yet fully familiar with it.
    Have a great day


    Stephen

  • Forum improvement

    Re: Forum improvement


    Hi All


    What about having a feature where we can select a previous page to view, other than the first 5, or the last one? So if I want to view page 12, then I can.


    Often I log on every few days, and something that has caught my eye is, by then, 5 or 10 pages in the past. Maybe I'm missing something, but I have to go back one page at a time, which is tedious.


    Thanks for your consideration


    Stephen

  • Send Email Without An Attachment

    Hi Dennis
    Thanks for this. I have modifed it somewhat to suit my needs, but I think I can follow it ok. There's always lots of syntax that I'm not familiar with, but each question and answer adds a little more.
    Cheers
    Stephen

  • Send Email Without An Attachment

    Hi all


    I'm new here, so apologise if this has already been asked/answered before.


    I use Office 200.


    I would like to send an email message to a supervisor when a user
    completes an order and pushes a button. The email needs to include
    some data relating to their order, all found on one row.


    The recipient is selected by the user from a dropdown list,
    and this will always be on the row of the last entry.


    I need to extract some data from the same row, and add the data to
    the body of the message.


    eg Item, Date Ordered, Lab or Plant, ETA, etc. I can do this part.
    (The subject will always be the same, eg "You Have A New Order".)


    At present when I use:

    Code
    ActiveWorkbook.SendMail Recipients:=Person, Subject:=Subject


    it sends the active workbook as an attachment.
    (I used this previously, and it worked fine.)


    Does anyone know how to send just an email with recipient,
    subject AND body text, but WITHOUT an attachment?


    Thanks in advance


    Stephen B

  • Replace #VALUE! error with text

    Hi Tested
    If a cell shows #VALUE it is because it cannot evaluate what you asked for.
    eg If A1 has a value of 5, and A2 has "No", then =SUM(A1:A5) will give #VALUE because it cannot add a number and text together.


    So you can get around this by querying if there is an error: =IF(ISERROR(SUM(A1:A2)),""No Data",SUM(A1:A2))
    Thus if there is an error, the formula returns "No Data", otherwise you get the sum.


    Similarly, for #DIV/0!, you can (should?) test to see if the denominator is 0. If it is then put "", else the result of the division.


    Hope this helps


    KiwiSteve

  • Inventory Assistance

    Hi EDR


    I think you and Derk have made good progress, so I'll leave it up to you guys to finish it. Yes, I was working on your first model, and I tried to stay true to your original requests, ie only 3 sheets. It made sense to have more, and I see you have now done this. It always happens, doesn't it? You start with an idea, and before long, once you see the possibilities, the idea takes on a life of its own, and suddenly the 'what-ifs' become a standard feature of the final idea. I regularly write Excel/VB things for people, only to have them modified untold before issue, so just come to expect it now...


    Trust it all works out fine for you.


    Cheers


    Stephen