Posts by Wattage

I need to seperate the time from a date & time field. When I try the RIGHT Fx, I get numbers that will not format into any time I can use. Please see my file for examples of what I'm dealing with and what I need.

Thanks for any help in advance.
Wattage

I have a large spreadsheet (350+ rows) that was sent to me from an outside source. The frist two rows actually ned to be one long row, they made them into two rows so the report could be printed on one page.:(

Is there a way to take the second row and merge it onto the end of the frist row and just change the column headings (I can do that manually)? I need something that will do 350 row convertions quickly.

I've attached an example of what I have and what I want to do. See text boxes.

I've searched the site for a similar problem with out any real results, so any help will be greatly appreciated.:)

Thanks,
Wattage

I'm trying to get a formula to give and yes or no answer if a cell is between two numbers.

Example: Cell AB9 = 100.2 in cell AB11 would be a formula that would answer "Yes" because it is between 96.8 and 100.4. Any number outside this range would answer "No."

An help would be great.
Thanks,
Wattage

Re: Formating multiple graphs the same

Shades,

Thanks - Your idea saved me several hours of work.

Wattage

I’m trying to get several graphs (14) to have the same look. I want all to have the same colors, fonts, and footers and headers. Is there a way to change the default graph format, or copy the format of one graph to another?

Any help is appreciated. :thanx:

Wattage

Re: Yes No calculations - automatically

Ger,

Thanks, your new formula worked great!! I apreciate it!!

The formula, when copied into the rest of the spreadsheet, leaves a "y" in the cell regardless if there are any "y or n" in the preceding cells. As I am counting the 'Ys and Ns," this will lead to a false percentage in my rate for cells that are blank.

Is there a way to have anything other than a Y or N as a place holder?

Thanks,
Wattage

Re: Yes No calculations - automatically

Ger,

I appreciate the effort. But "=If(And(B4="n",C4="n"),"n","y")" did not work. I need a formunla for cell D4.

I'll try to simplify the responses I'm looking for based on Col#1 and Col#2 answers. Column #3 would calculate a Y or N based on Col1 or Col2 answers:

Col#1 Col#2 Col#3
y + n = y

n + y = y

y + blank = y

Thanks,
Wattage

Hello,

I’m trying to put together a spreadsheet that calculates rates based on certain answers to yes or no questions. In the example that I’ve attached, there are two columns that can have a Y or N answer. If the first column is Y, then there should be a Y automatically placed in column 3.

Any Yes answer in column 1 gets an automatic Y in column 3. The problem is if there is a No in column 1 and a Yes in column 2. This is a valid exception and should also get an automatic yes in column 3 (see yellow row). Any No response in Column 1 gets an automatic N in column 3.

This spreadsheet may eventually have 200 rows.

Any help in getting this to work will be greatly appreciated.

Thanks,
Wattage
:thanx:

I've got a large spreadsheet that is filled out by others and automatically creates a bar graph. All this works very well. What I'd like to do is add an "N" value to the graph so anyone viewing the graph knows how many "items" are included without having t go back to the spreadsheet.

Anyone have and idea on how to add a field to at graph that would be automatically updated as the spreadsheet changes?

Thanks,
Wattage
:thanx:

Re: Format time in a column automatically

Mattenborough,

THANKS - works fine. I added a couple of columns and its really speeding things up for us. :thanx:

Wattage

Hello All,

Is it possible to format a cell for "time" to where I would NOT have to enter in the colon for time ? (ie, 13:30.)

I have to enter a lot of time elements into a spreadsheet and it would speed things up if all I had to type in was 1330 and Excel automatically formatted it to 13:30.

Any ideas?

Thanks,
Wattage from Virginia USA

Re: Cell Restrictions and Rate Calculations

Barbarr,

THANKS - It worked great.

It took me some time to expand the sheet to 200 rows but I got it working!

Thanks again
Wattage

Re: Cell Restrictions and Rate Calculations

Thanks again - Wattage

Re: Cell Restrictions and Rate Calculations

Barbarr,

Thanks for all your work, I appreciate it.

I tried your formulas and they worked up untill I tried to add or change entries. I enter three Y in the eligibility cells and a Y is created in Column D. But when I enter a Y in "Service Provided" I get the following message:

"If eligible AND Serviced, then enter Y
If eligible AND NOT Serviced, then Blank
If NOT Eligible, then leave Blank"

It will not let me enter anything in the cells after this.

Any suggestions?

Thanks again,
Wattage

Re: Cell Restrictions and Rate Calculations

Barbarr

Column E answers whether or not the client received the service we are counting. We’re counting the number of people we provide certain immunizations to.

Columns F,G, and H are exclusion criteria that if answered “Y” allows us to count the person as a successful immunization administration – like they already had the shot. Three Ys plus one more Y equals four Ys and that’s counted a success.

If the client does not meet the first three blue cells (columns A,B,C) inclusion criteria, we don’t count them at all. We only count those who meet all three (those with a 3 in column D). I don’t know how to do drop boxes in Excel. The staff will just type in a letter “y” or “n” in the cells.

Our goal is to provide immunizations to all our clients that are eligible. If they are eligible and do receive the shot a “Y” goes in column E (nothing else should be in columns F-H). If they do not receive the immunization (“N” in column E), and do not meet any of the exclusion criteria, we failed and it count towards are failure rate.

Thanks,
Wattage

I am trying to develop a counting tool that allows us to track results related to some survey questions. To be eligible to be counted three criteria questions must be answered with a Yes (blue shaded cells). Then a service is either provided or not (Y or N). However, there are three exclusionary criteria that allow the respondent to be counted as if they received the service (green cells).

The attached worksheet shows how I've "counted" everything so far. I’m only interested in Eligible clients. My question is how can I keep track of only eligible patients and those who meet the exclusionary criteria. Is it possible to restrict entry on the green cells to just one answer (either a Y or N) or restrict entry if they do not meet the eligibility requirements of a 3? Notice the false failure in row 12 – I’m also trying to eliminate theses.

I then need to calculate the success and failure on the eligible clients (yellow cells).

Or is there a better way to count and calculate this data?

Any help is appreciated.

Re: Calculating Time and IF statements

Batman - THANKS!! - worked perfectly. I appreciate the help a lot - wattage
h1h & Shosmeister thanks for your help as well

Re: Calculating Time and IF statements

H1H - thanks for the effort!!

I tried H1H's suggestions, but I keep getting a error message about the formula. Excel does not like the C>0 element of the formula.

Any other suggestions?

Wattage

I’m trying to calculate time and develop a couple of Yes or No statements.

I am able to calculate the difference between two times, which can be positive or negative. If the difference is <=60 I need a Yes statement in a cell, If the difference is >=61 then I need a No statement in a cell. Now comes the first tricky part - I also need a No statement if the time is <=0.

Yes = 1 minutes to 60 minutes
No = 61 or greater
No = -1 minutes or less (7:20 – 7:10 = -10 minutes)

Here is the second tricky part, while there is nothing in the cells (null), I need something other than a Yes, or No in the cell. This will enable me to calculate my denominator later –which will be the total number of Yes and Nos.

Here is the cell I’m currently using =IF(K33*1440>0,IF(K33*1440<60,"Y","N"), "N") time format is “custom [mm];-[mm].” This came from another member of this forum (uandras) and works great except for the second trick part. It puts No in every empty cell.

Eventually I want to calculate the rate of Yes as follows: Num Yes / (nub Yes + nub No)= X%

I've attached an example of what I'm working with.

Any help is appreciated.
Thanks - Wattage

I am trying to count a range of times. I have two times that I subtract to see if the difference is under an hour i.e. 7:15 to 8:00= :45 min. I have a couple hundred of these calculations. I need to count all the cells with 60 minutes or less in them. I would prefer a simple cell formula that would produce a “Y” if a cell if <=60 min or a “N” if > 60 min. I also need to count as “N” any time that is less than 0 i.e. 8:10 to 8:00 = -10 min or 10 min late. I can count the “Y”s and “N” later.

Example of what I'm trying to do is below. Thanks for any assistance.

Time 1 Time 2 Result Pass/Fail
7:00 7:45 :45 Y
7:00 8:15 1:15 N
8:10 8:00 :10 N