• Welcome to OGBoards 10.0, keep in mind that we will be making LOTS of changes to smooth out the experience here and make it as close as possible functionally to the old software, but feel free to drop suggestions or requests in the Tech Support subforum!

The Excel Help Thread

Conditional formatting question. I know how to create a rule to format a certain cell to fill green when a certain date is reached within that cell, but how to you do it when another cell reaches that date? For example, I want cell A1 to fill green when cell C1 reaches the date contained within.

I googled it and came up with this: http://www.techonthenet.com/excel/questions/cond_format4_2007.php which is how I learned how to fill a cell on a date, but no examples of what I am looking for.
 
Should be as easy as setting the formatting of cell A1 based on the formula =C1>=X where X is either the date to which referring or the cell that contains that date
 
Last edited:
I cannot get a damn median from excel for this set of numbers. Would or could anyone please help me find the median?

Here they are:
 

Attachments

  • Access Writing Datat Set.xlsx
    10 KB · Views: 0
So, what is the difference between numbers and text in Excel? Texas, how did you run the formula. I can do it by hand, but I would like to figure out how to run the damn formula correctly. Thanks for the help, both of you.

All the cells are formatted as text cells. You just have to change the formatting to numbers.
 
Change the text to numbers. Try this.

Method 4: Use the Paste Special Command

To use the Paste Special command, follow these steps:
In any blank cell, type the value 1.
Make sure the cell you typed 1 in is formatted as a number.
Select the cell in which you typed 1, and then right click and choose Copy.
Select the cells with the values that you want to convert to numbers.
Right click and choose Paste Special.
Under Operation, click Multiply and then click OK.
 
So, what is the difference between numbers and text in Excel? Texas, how did you run the formula. I can do it by hand, but I would like to figure out how to run the damn formula correctly. Thanks for the help, both of you.

I did it in R
 
I have a three column spreadsheet involving dates and the amount of time passed. Column A is the date of origination, Column B is a formula calculating todays date, Column C is a formula that spells out the number of years, months, and days between Column A and Column B (today). An example is below

Column A = 2/1/1973

Column B = TODAY()

Column C = =DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months, "&DATEDIF(A2,B2,"md")&" days"

I'm hoping to merge this sheet with another with multiple other piece of information and ideally it would not have a column solely devoted to todays date. Is there a way to replace the current date column (B2) with a formula in column C that will account for today's date without needing another column? I tried to go in and simply replace B2 with TODAY, then TODAY() and neither worked.
 
I have a three column spreadsheet involving dates and the amount of time passed. Column A is the date of origination, Column B is a formula calculating todays date, Column C is a formula that spells out the number of years, months, and days between Column A and Column B (today). An example is below

Column A = 2/1/1973

Column B = TODAY()

Column C = =DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months, "&DATEDIF(A2,B2,"md")&" days"

I'm hoping to merge this sheet with another with multiple other piece of information and ideally it would not have a column solely devoted to todays date. Is there a way to replace the current date column (B2) with a formula in column C that will account for today's date without needing another column? I tried to go in and simply replace B2 with TODAY, then TODAY() and neither worked.

I believe you should be able to get it to work by replacing B2 with TODAY(), not sure why you are running into issues, could you try:

=DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months, "&DATEDIF(A2,TODAY(),"md")&" days"
 
=DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months, "&DATEDIF(A2,TODAY(),"md")&" days"

This worked, thanks! I tried TODAY() yesterday for it kept returning #help. For some reason I copied and plugged in the formula you posted and it worked flawlessly. Who knows, maybe I input an error on my initial formula. I'm glad I was at least on the right track.

Thanks again!
 
Anyone want to help me figure out this macro?

I want to search through range C2:AY4514 for instances where there are duplicate values AND the value in the respective B column is different.
 
Needing some VBA help. I'm working on a simple exercise where the data is a sample of test scores and the exercise is to calculate some basic statistics about the scores (Avg, StdDev, Min, Max). That part is easy enough. I'm having trouble with my declaring and setting my range object. Here's a snippet of the code:

Sub CalcScores()
Dim scoresAverage As Single
Dim scoresStDev As Single
Dim scoresMin As Integer
Dim scoresMax As Integer
Dim scoresRange As Range


'Set scoresRange = ActiveWorkbook.Worksheets("wsScores").Range("scores_dat")
'Receiving error message: Runtime Error 9>Subscript out of Range when using code above
'I used the longform code based on the example in the book / slides, but it errored - why is that?

Set scoresRange = wsScores.Range("scores_dat")
'No errors; not sure what's going on?
'What is the most appropriate method for setting range after declaring it?


Any thoughts on why the long/full qualification won't work?

ETA: worksheet and range alias is accurate, so no issues there. The rest of the macro runs using the shorter qualification.

Solution: using the worksheet properties and setting the codename to "wsScores" created a worksheet object. In the full qualification that was creating an error, my command was looking for the worksheet name - the tab showed the default "Sheet1" name. This was the cause of the error. So, to fix this you can declare the worksheet object by using "Set" or by using the codename (which I did). So for the ones of you who care, I got an answer to my problem and figured I'd share it here.
 
Last edited:
I think that error happens when you haven't specified the # of elements in the array you assigned.
 
I think that error happens when you haven't specified the # of elements in the array you assigned.

In my case, the error occurred because the qualifying line:

Set scoresRange = ActiveWorkbook.Worksheets("wsScores").Range("scores_dat")

Was looking for the name of the worksheet (the visible name that appears on the worksheet tab). I should have used "Sheet1" since that was the worksheet name; VBA "couldn't find" a sheet named wsData.

I ended up creating a worksheet object using the properties/codename and setting the value with the shorter line of code:

Set scoresRange = wsScores.Range("scores_dat")

That approach ended up fixing the macro.
 
I'm trying to calculate occurrence rates per month since a start date to today and also the rate needed to reach a goal by a particular end date to today. I've searched google and most tutorials involve calculating time between dates, etc. Any ideas on a formula that calculate the figures for columns E and F?


Start Date# OccurrencesTarget NumberEnd DateRate of Occurrences per Month Since StartRate of Occurrences Needed to Reach Goal by End Date
1/1/166249/6/16????
2/2/162520012/6/16????
 
(# occurrences)/(month(today())-month(start date))

(target number-#occurrences)/(month(end date)-month(today()))

could add a column with a =today() formula and reference that. I think that should work

Start Date # Occurrences Target Number End Date Rate of Occurrences per Month Since Start Rate of Occurrences Needed to Reach Goal by End Date today
1/1/2016 6 24 9/6/2016 =B2/(MONTH($G$2)-MONTH(A2)) =(C2-B2)/(MONTH(D2)-MONTH($G$2)) =TODAY()
2/2/2016 25 200 12/6/2016 =B3/(MONTH($G$2)-MONTH(A3)) =(C3-B3)/(MONTH(D3)-MONTH($G$2))
 
Back
Top