Excel 2000

Question

Is there a formula for calculating a square root or cube root in2000? I was setting up a form for solving quadratic equations and one column calls for a square root which I have to do manually. I should be grateful if you could help. Thank you. Paul W Hunt

Answer

Paul W Hunt

there is a sqrt function

but in general, the square root can be taken as raising the number to the 1/2 power

=A1^(1/2)

cube root to the 1/3 power

=A1^(1/3)

So if 27 is in A1, this formula produces 3.

-- Tom Ogilyv

Question

I have a worksheet that has a DOB column and a entry date column. I did a formula to figure age (=(a1-a2)/365.25). I formatted the column to number with zero decimals. On the worksheet the age comes back to a round number but when I create a pivot table to count the ages it is pulling the age with a decimal and 5 numbers after it, example 54.088898 which means if I have another 54 with different numbers after the decimals it is counting each one. I want the pivot table to count off whole numbers. Can you please help

Answer

Assume that the A1 cell is the current date and the A2 is the DOB. I would suggest you to use Dated if.

This will get you the Number of Years between DOB to till date =DATEDIF(A2,A1,"Y")

This will get you the Number of Months between DOB to till date =DATEDIF(A2,A1,"M")

This will get you the Number of Days between DOB to till date =DATEDIF(A2,A1,"D")

If suppose you would like to as per your formula then add a round function on it like the below:- =ROUND((A1-A2)/365.25,0) Change the 0 to your desired number of decimal value. But here the days per year has been mentioned as 365.25 so every 4 years it will be accumulated and added as 1 day, but by way of mentioning the 365.25 as the constant value for a year in the above formula will not get the correct Month or Date difference between the DOB to till date.

So I strongly recommend you to use the Datedif function.

Question

I have to check cells in a worksheet to see if they are empty or if not to run a macro. I have tried:

If(And((T4=""),(T25="")),"", Run macro)

I have used dummy values for "" and Run macro and the principle seems to work.

Can you tell me how to run a macro from this statement - or is there a better way of doing it

In the macro I need to find the font colour of the ActiveCell so that I can make decisions on further action in the macro. Colour will be Red or Blue. Could you also help with this please? Alan

Answer

Well, one question at a time ... :)

I don't understand the second question, so please be more specific, example, and detailed and repost it.

As to the first question, no, you can't call a code routine from within a worksheet formula. What you mean by "the principle seems to work" ? How is it working? I do not believe this is possible, I have not heard of a formula having the ability to fire code.  :) Code fires code.

So you need to check the worksheet, and if t4 and t25 are both blank, then run a macro?

When do you want to check?

If on an as-needed basis, then put this code in a standard module, and of course attach it to a button if you want. If every time the worksheet changes, then use the second block of code I'm posting below, and put it in the worksheet's code module of the worksheet in question.

Obviously, adjust both with the name of your macro, and anything else you need to adjust.

____________________________________________________________________________________________

Sub runMyMacro()

If Len(ThisWorkbook.Worksheets("Sheet1").Range("t4")) < 1 And _ Len(ThisWorkbook.Worksheets("Sheet1").Range("t25")) < 1 Then

Call mymacro

End If

End Sub ____________________________________________________________________________________________

____________________________________________________________________________________________

Private Sub Worksheet_Change(ByVal Target As Range)

If Len(ThisWorkbook.Worksheets("Sheet1").Range("t4")) < 1 And _ Len(ThisWorkbook.Worksheets("Sheet1").Range("t25")) < 1 Then

Call mymacro

End If End Sub ____________________________________________________________________________________________