Re: Colouring Part of a Chart Series
I'll submit a chart tomorrow when I have a moment. The file is big, so I'll chop it down.
Re: Colouring Part of a Chart Series
I'll submit a chart tomorrow when I have a moment. The file is big, so I'll chop it down.
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
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
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
Re: Solving for Unknown Variable
What about using Solver? It's easy to set up and works in a second or two.
HTH
Stephen
Re: Tricky Formula
Also, if A24 is formatted as text, but in the lookup it is a number, it won't find it.
Stephen
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
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
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
Re: Significant Figures Function
Hi Jon
This is brilliant. Thank you.
Stephen
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
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
Re: Combinations From A List
Hi there
Here is a solution to your problem. Could probably be fine-tuned by some whiz, but certainly does what you want. It works for up to 16 names, which you enter in the blue cells.
Have fun
Cheers
KiwiSteve
Hi Domenic
Just browsed through here and found your really useful formula to sort a list into rank order. Brilliant! Thanks
KiwiSteve
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
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:
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
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
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
Hi EDR
I think I have a straight-forward solution. Give me another day and it'll be done.
Cheers
Stephen
Sorry, should have added:
1.1 = (100%+10%)
for 25% increase use 1.25, etc.
Decreasing by a % goes like this:
0.80 = (100%-20%), etc.
Cheers
KiwiSteve