• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Timeline Formula Problem

R

rtdouglas

New Member
I am trying to a timeline but am having trouble with creating a formula that will recognise when there is an entry against a particular date and which will automatically select the next height in an array for that entry so that each entry on the timeline will be at a different height each time.

I have worked out most of formula as below:

=IF(B8="",0,INDEX(Position,1,1))

The trouble is getting the formula to automatically allocate the next number on the array in a repeating loop. I have seen some formula's using the MOD function which seem to be almost what I am after but can't quite get them to work.

Any suggestions?

Thanks
 

Attachments

Hi, rtdouglas!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, I don't understand what's in the file that you uploaded, if it's the input data, the output data, or both. Would you please clarify that and if it's missing include manual examples of desired output? It'd be very useful for those who read this and might be able to help you.

Include what do the values at B2:B5 mean, and which is the concept of the height value of column C.

Regards!
 
Sorry, not been very clear. Rookie mistake.

Essentially what I am trying to do is get a formula which will recognise when there is an entry in the range B8:B38, and when there is, to automatically assign the next figure in the Position array - i.e. the first entry will have a height of 50, the next 100, the third -75 and the fourth -40 in a continuous loop. When I create the timeline from this data the height of the events on the y axis will then be as shown in the picture.

I believe I almost there with the formula above but not quite. There may well be other ways of doing this but I want to ensure that I can get to stage of having an expandable date range into which I can add dates as they come up without having to add the height of the new item (and consequently the remaining entries at later dates) manually.

Thanks
 

Attachments

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