yogi_From A Table of Tasks And Completion Status Compute Percent COMPLTE And NOT COMPLETE

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #735   Sep 05, 2012     www.energyefficientbuild.com.

user Daltonj said: (http://productforums.google.com/forum/?zx=4xb2p2lt76nu#!category-topic/docs/spreadsheets/8du8FD2TO-c)
Code to figure percentage complete

Hey

I'm setting up a to-do list and I'd like to have a piece of code in one cell that counts all the cells with a task in them in the task column, say B2;B(however you say infinity). It's also going to need to have some kind of SumIf statement, because it needs to check an adjacent column to see if I've said that "Yes" I have completed the task. I think I could use this code for multiple things, but that's what I need it for at the moment.

To sum it up. I have two adjacent columns with values starting in the second row and I have an open ended number of rows it could contain. I need it to check all of the tasks to see if I have completed them and then in one cell of my choice it will display the percent out of 100 that I've completed. So if I have 10 tasks and I've completed 6, then I'll have the string "Yes" beside the completed task and one cell will display 60%. Also, just for knowledge sake, I'd like to know how to inverse that correctly and be able to say 40% left. Thanks everyone.

Daltonj

--------------------------------------------------------------------------------------------

following is a solution to the problem

yogi_Set Up A Book Reading Program With Starting Date Time Interval In Days Number Of Intervals And Progress Report

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #725   Sep 01, 2012     www.energyefficientbuild.com.

user DaltonJ said: (https://productforums.google.com/forum/#!mydiscussions/docs/MbgLk9V0sCw)
Issues with a formula: Date countdown function

Hey everyone

I've got a little issue with something I'm trying to do for my brother. The particular issue I'm trying to fix is with a function dealing with dates. I'm trying to set up about 7 cells in a column to count down days in two week periods. For instance, each cell would start out saying 14. This would stand for 14 days. Each cell would be for a 2 week period. If I started down the countdown from today, I'd want it to countdown from 14 to 0 and that would be two weeks. The day after it hits 0, I would like the two week period in the next cell to begin counting down, and so on.

So if I have 7 cells, each starts off displaying 14. They countdown to 0, then the next cell takes over and proceeds till they've all counted down to zero. I've tried different formulas and keep finding logic errors if not just outright errors that break it. The code I've gotten to now is the following:

=if(date(2012,09,12)-TODAY()<=0,0,if(date(2012,09,12)-TODAY()>14,14,or(date(2012,09,12)-TODAY()<14;date(2012,09,12)-TODAY()>0),date(2012,09,12)-TODAY()))

I looked around and it seemed as if the best way to do it was with nested IF statements. The red section is my first statement. It says that if the target date, minus today's date, is less than or equal to "0", then display zero. This keeps it from going into the negative numbers. The green section says if it's greater than 14, then display 14. I had a problem when I went to the second cell of it being 28 and showing that. It was a logic error. In the blue section, I needed to use an OR statement. I'm not sure if that's legal. I'm not 100% sure how to use the IF statement, but this is how they showed to nest it in another post on this forum. I had to use the OR statement, because I needed to tell it that if the target date, minus today's date, is between 14 and 0, then display it. This would be my 13-1 numbers. If anyone could tell me the most consice way to write this code I would appreciate it. Also, while you're here, there are a couple of other things I'd like to do involving this.

This whole thing is because he wants to read a book series, but has trouble without a deadline. I've set up a whole thing that tells him his average number of pages he's read, how many he has left in each book and total, etc... This issue is because I'm trying to give him 2 weeks per book. I'd like to be able to change that later, but it's what i'm going with now. I'd like to be able to display a message in an adjacent cell depending on how he's doing. Ideally, I would want a formula that would take the number of pages he's got left to read and if it falls within a certain threshold, then display a particular message. For example, if he's halfway through the book and has a week left of his two weeks, then it would say, "You're doing well"; if he has read half of the book and only has 2 days, then it would say, "Hurry up, you're falling behind". If he's finished the book, it would say, "Congradulations, you're done!". That kind of thing. At minimum, I'd want to display these messages based on what number is displayed in the 14 day cell.

Second, I'm currently changing the date in each cell that the 2 week period starts in. In the case above, it would be date(2012,09,12). For the next cell, I would change it to date(2012,09,26), etc... I'd like to have that 2 week period to extrapolate from a cell that he would enter his start date in. So if he were to start today, on 8/30/2012 (aka Cell A1), then the first cell would be date(A1 - today()), then the next countdown cell would be like, date((A1 + 2 weeks)-today()), and so on. I tried to pull the date from a cell for another simpler formula and it wouldn't do it. I think it was in the wrong format.

If you guys/gals could help me with these problems, I would really appreciate it. Thanks for reading all of this.

----------------------------------------------------------------------------------------

following presentation is a result of a little play I had with this