In over my head

i8ig

Board Regular
Joined
Jul 17, 2007
Messages
122
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Gary Drumm

Active Member
Joined
Feb 22, 2005
Messages
462
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

Board Regular
Joined
Jul 17, 2007
Messages
122
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

Board Regular
Joined
Jul 28, 2007
Messages
96
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
 

cpis0002

Board Regular
Joined
Jul 28, 2007
Messages
96
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
 

Forum statistics

Threads
1,181,648
Messages
5,931,213
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top