hi
i have a spreadsheet of run and cycle data. I am trying to set up a formula that will calculate:
* the run or cycle number (starting at 1)
* the number of days since a previous run, or previous cycle,
* the total weekly miles for cycling and running
I've attached an example spreadsheet, which shows some basic run/cycle data on the left hand side, and on the right are blank spaces that I want calculated. Can anyone assist with the formulas I should be using?
I am using excel 2003.
Cheers
Dean
can anyone help with this formula? is there any other info I could provide that would help?
cheers
Dean
Hi Deanomcbeano,
On Sheet 2 I have changed the Sheet layout a little to display the Date in a single Column. Have a look at it and see if it now shows what you want.
Also see the "Please consider" note at the bottom of this post.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
Hi Winon
Thank you for the response. I was hoping to calculate the results per week, i.e. total runs for week 38, total for week 39, rather than cumulatively. I've included an example sheet with the data as it should work out - but without the formulas. Would you be able to take a look?
Regards
Dean
Hi
In this case(as you use Excel 2003), SUMPRODUCT, maybe works for you.... I was hoping to calculate the results per week
Unfortunatelly the results are not the same as these that you put manually to your examle.
Do you mind to take a look to my sheet example?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hi Fotis
Thanks for the assistance. The columns K and N are calculating correctly, and I can use that formula. However columns I, J, L and M are not right - the totals should be cumulatively increasing for columns I and L. Whilst for columns J and M the days between each run should only be in single figures as in my example.
Any further suggestions?
Cheers
Dean
Hi Dean.
.The results in Columns I & L that you want to get, you can get these, using a COUNTIF, as first suggested by Winon. But in this case you don't have result per week. You have total result...... I was hoping to calculate the results per week
In I2 and copy down to I8, put this formula.
=COUNTIF(B2:$B$8,"Run")
This gives you the result that manually, put there. Is this correct?
Hi Fotis
Thank you! That sorts out columns I and L - actually that was a pretty simple formula - should have sorted that myself. Any suggestions for calculating the days as I want in columns J and M?
Cheers
Dean
Let's try this.
In J2 and copy down, to J8.
=IF(ISERROR(E2-INDEX(E3:$E$8,MATCH(B2,B3:$B$8;0))),"",(E2-INDEX(E3:$E$8,MATCH(B2,B3:$B$8,0))))
That's come up with an error.
What kind of error?
Wow!,
This turned out to be more complicated than what it appeared to be. Thank you for helping out here Fotis.
Sheet 2 of the attached WorkBook, is the best I can come up with. I think, we should maybe consider splitting the Data as shown in the other Sheets I've created. And use Sheet 1 as a Summary of the Data.
Just an idea to simplify things a bit. Look at it, and see if you like.
Last edited by Winon; 10-02-2012 at 06:18 AM.
thanks guys, i'm still working through some of the changes, can't quite get everything i want to work as it should just yet. will come back to you if needs be.
cheers
dean
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Deanomcbeano 
Register To Reply
Bookmarks