Replies

Hi all. and thanks in advance. This forum has proven invaluable previously and I’m expecting the same again.

I have what appears to be a simple task. I need to count the number of records associated with a particular year from a “database” in excell. There are over 21000 records and one of the “fields” is created date which is entered as a date from 1/1/2006 through 7/7/2009. I need to know the number of records with a created date in 2006, 2007, 2008, & 2009. Can I use a countif statement? or do I need to look at sum, with if statements.

Not sure how to proceed to get only those records for each particular year.

Any help to this still learning newbie is appreciated. Thanks for all the past help and for the anticipated help on this one. All contributors here rock. Here’s to each and every one of you

Fred

Hi

To answer your question, yes, you can use the COUNTIF formula like this
Assuming your Create Dates are in Col A

In C2, enter this for the year 2006
=COUNTIF($A$2:$A$101,”>=”&DATE(2006,1,1))-COUNTIF($A$2:$A$101,”>”&DATE(2007,1,1))

In C3, enter this for the year 2007
=COUNTIF($A$2:$A$101,”>=”&DATE(2007,1,1))-COUNTIF($A$2:$A$101,”>”&DATE(2008,1,1))

In C4, enter this for the year 2008
=COUNTIF($A$2:$A$101,”>=”&DATE(2008,1,1))-COUNTIF($A$2:$A$101,”>”&DATE(2009,1,1))

In C5, enter this for year 2009
=COUNTIF($A$2:$A$101,”>=”&DATE(2009,1,1))-COUNTIF($A$2:$A$101,”>”&DATE(2009,7,7))

the last formula will count up to 7/7/2009 as specified, if you want to count for the full year of 2009
change the Date(2009,7,7) to Date(2010,1,1)

I tried using cell’s references but its doesn’t works.
If your dates will grow infinitely, Pivot Table is the way to go as have been demonsrated by Hans

Hope this help

Let’s say that A2 contains the text January Sales, and that B1 contains the text K37.
The formula then evaluates to
=INDIRECT(“‘January Sales’!K37”)
which is equivalent to
=’January Sales’!K37
This returns the value of cell K37 on the sheet named January Sales.
The single quotes (apostrophes) are used around the sheet name; they are required if the sheet name contains spaces or unusual characters. If the sheet name is a single word, the single quotes aren’t required but they won’t hurt either.

Hi Hans

Thanks for the explantion

I have read this thread and don’t undersatnd what you want to achieve.
Does the formulas in your attachment show the result you want?
If yes, what are you after?
If no, I suggest that you post a new sample with your original data in a table
and another table with the result you are after.

I am trying to inset a IF statement that inserts 7% if cell is greater than 99999 and inserts 5% if cell is less than 100000. I would really appreciate any help.

Hi

I am confused on your requirement
if the cell >99999 and 99999 or < 100000, what result would you want?

Hans

Thanks, its work extremely well.

I appreciate your time and effort on this

Sorry, the Lounge automatically converted ( c ) to ©. I have corrected this; the line should have been

If IsNumeric(c) Then

The code should be run in Word, with the document open.
After running the macro, switch to Excel and press Ctrl+V to paste the table into your workbook.

Hans

Thanks, this work well. Is it not possible to run this codes from Excel?

TIA

Here’s a macro that you can run in the Word document. When it finishes, there will be a table on the clipboard ready to be pasted into an Excel workbook.
You can add the header row there and clean up the formatting.

[codebox]
Sub ConvertDoc()
Dim c As String
Selection.HomeKey Unit:=wdStory
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.MatchWildcards = False
Do While .Execute(FindText:=”^p^p”, Replace:=wdReplaceNone)
c = ActiveDocument.Range(Start:=Selection.End, End:=Selection.End + 1).Text
If IsNumeric© Then
Selection.Text = “@@@@”
Selection.Collapse Direction:=wdCollapseEnd
.Execute FindText:=”.”, ReplaceWith:=”^t”, Replace:=wdReplaceOne
Else
Selection.Text = vbTab
Selection.Collapse Direction:=wdCollapseEnd
Selection.Extend Character:=”)”
Selection.Delete
End If
Selection.Collapse Direction:=wdCollapseEnd
Loop
Selection.HomeKey Unit:=wdStory
Do While .Execute(FindText:=”^p”, ReplaceWith:=”^t”, Replace:=wdReplaceOne) _
And Selection.End < ActiveDocument.Content.End
Selection.Collapse Direction:=wdCollapseEnd
Selection.Extend Character:=")"
Selection.Delete
Loop
Selection.HomeKey Unit:=wdStory
.Execute FindText:="@@@@", ReplaceWith:="^p", Replace:=wdReplaceAll
End With
ActiveDocument.Content.ConvertToTable Separator:=wdSeparateByTabs
ActiveDocument.Tables(1).Range.Copy
End Sub[/codebox]

Hans

Thanks for providing a solution
I got an error error message in the line highlight in red above
It is telling me that variables not define

Just to clarify that I am to run this in Word and not Excel?

TIA

Hi,

Please find attached a report where % ranges indicate the Colour.

So the target is 95% for all students.

How can I get excel to generate the colour (traffic light concept)?

I’m not sure if this is a Maths question or an excel question, but if I’m to use a simple formula, do I calculate the % against the target (i.e. 95%), or against the whole (100%)?

From my understanding, it’s the target.

TIA.

Adam

Not so clear as what you want to achieve, but see attached if this is waht you are after
this assume there is no blanks

Maybe I need a little help understanding your directions.
Your attached file is exactly what I want, but I can’t seem to get this to work on my file.
Thanks very much,
HandyAndy

At which part of the above you are not sure of?
or you can use the attachment I have provided

Open the attachment
Select the area highlighted
Go to the Menu bar
Select Format, then choose Conditional Formatting

from there, you will see how the formatting can be done.

Let me know if you still can’t get it.

Hi All

As in the description… it doesn’t matter what the actual result would be in the cell how do I get excel to recognize if it is more than half way to the next thousand in this case to roundup?

Tia
cvbs

try looking up ROUND in the Excel Help

your answer is in there

try this

1) Select the area in which you wish to highlight
2) Go to the Format menu and select Conditional Formatting.
3) In Condition 1, select “Formula Is”
4) Next to it, type in “=MOD(ROW(),2)=1”
5) Click on the Format >> Patterns tab.
6) Select the color you want
7) OK >> OK

The area you selected should have every other row showing color you have selected
If you add or delete a row, the shading remains consistent

The attached shows what you are after

Let’s say you enter “red” (without the quotes) in F2, and the other color names below it.
In G2, enter the following array formula (confirm with Ctrl+Shift+Enter):

=MAX(IF($B$2:$B$65536=F2,$C$2:$C$65536))

Fill down as far as needed.

See the attached sample workbook.

Array formula will affect the performance if you have a long list going into
ten of thousands.

Using Han’s sample,

here a shorter version, but this is still an array formula. ( confirm with Ctrl, Shift and Enter )

=MAX((B2:B10000=F2)*(C2:C10000))

another non array formula but you can’t use whole column

=LOOKUP(2,1/($B$2:$B$10000=$F2),$C$2:$C$10000)

HTH

I need assist to do the following per the attached workbook

1) I need to calculate the cumulative cash flow using the AND function as a logical alternative

2) I have calculated the payback period, but need help to calculate the payback using an alternative method using a nested if function for cell C93:J93

Your assistance will be most appreciated

Regards

Howard

It is unclear on what you want to do
would you stated what are the criterias?

=IF(AND(AP$3>=$J6,AP$3<=$K6),$M6/$L6,"")

But get a result error.

Attached is an example sheet with all field available – formula in in the cells in yellow.

Any help appreciated.

Regards

Mike

Mike

nothing wrong with the formula and the result are correct in your example.

what is the error you get?

I’m looking for something like COUNTA() that will count the number of unique values in a column. It would be OK if the column had to be sorted first, but I’m hoping for something better.

When you turn on AutoFilter in a worksheet and click the arrow on the dropdown box at the top of any column, you see the unique values right there and instantaneously. That leads me to think that the function I want might already exist, or at least be easy to create.

What say the Gurus?

One other way with non array formula ( just press Enter )

assume your range start from col A row 2

=SUMPRODUCT((A2:A100″”)/COUNTIF(A2:A100,A2:A100&””))

HTH