In over my head

[i8ig]

I have a spread sheet that has information in numbers, text and dates. I need to be able to write a formula in a cell K30 that does the following:

Look at column 21(U) and if "Settled" appears to count the number of times this occurs, then to take the total and divide it by the number that is a result of: Looking into Column 1(A), finding all the rows which have 'Nbar' and then Looking into column 13(M) and if the date in this column that corresponds to the row with 'Nbar' and the date is =to today or before today and place the result in K30. The only other variable is that no cell past the cell in Column 21(U) where the last 'Settled' appears be counted. I would like to have this cell update automatically as 'Settled' is entered.

I have no idea where to start.

Any help is appreciated, and thanks in advance

 

[Gary Drumm]

Can you post a sample of your sheet, and give an example of what you want the expected result to look like?

At this point, I'm not clear on exactly what you want it to do.

Use Colo's HTML maker.
Gary

 

[i8ig]

I am unable to post sheet due to company not allowing down load of Colo's HTML maker. I could email you a sheet if that will help?

What I need is a number in K30 that would be the sum of the formula I asked about

 

[cpis0002]

Hi, I didnt understand the last sentence... as for the rest:

Create a column and drag this formula throught the column:

=IF(AND(A10="Nbar",M10<=$B$1),"Y","N")

Just change the row number in order to select the rows you have. $B$1 is a fixed reference cell with the formula =NOW()

then use the formula:

=(COUNTIF(U10:U500,"Settled"))/(COUNTIF(range of new column,"Y"))

Obviously adjust the range for the data you have. If you need anymore assistance or would like me to apply any of these changes, just send me an email on chrispisani@gmail.com

 

[i8ig]

Thanks very much, I now can see how this works!!

 

[cpis0002]

no probs, if u need anything else, just lemme know... i love a challenge... as long as it has nothing todo with visual basic, lol