Programming

Programming questions? Ask a programmer for answers ASAP

Ask a Computer Expert,
Get an Answer ASAP!

I am trying to cull data points from multiple sources that…

Customer Question
I am trying to cull...
I am trying to cull data points from multiple sources that are calculating milestones in days and then average them. However, when only the first part of the calculation has happened, for example First patient identified to last patient identified, and only the first patient has been identified, it comes up with a negative number. Then when the averages are calculated it's taking that negative number into consideration even though it's only one trial that has completed that item. So the averages are all messed up. Is there an IF THEN statement I can create that will eliminate the negative numbers from being included in the calculation?
Submitted: 8 years ago.Category: Programming
Show More
Show Less
Ask Your Own Programming Question
Answered in 15 minutes by:
6/13/2010
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago
John D
John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9,666
Experience: Vast experience in Excel programming and business solutions
Verified

Hi,

 

Assuming your list is in column A starting from A1 to A99, use the following array formula to average only the positive numbers:

 

=AVERAGE(IF(A1:A99>0,A1:A99))

 

Please note that since this is an 'array' formula you need to press Shift + Ctrl + Enter keys together instead of just the Enter key after entering the formula.

 

Hope this helps Let me know if you have any questions

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago

Unfortunately, in looking at the data, there are in fact numbers that need to be included in the calculations that ARE negative: For example, protocol signature date to vendor contrac is negative....additionally there are numbers that due to the calculation of dates are positive (large positive) when a part of the calculation is missing....any thoughts?

 

Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

There must be a way to identify which numbers should be included and which numbers shouldn't.

 

So what is the rule?

 

Also is there anything from the adjacent columns that can tell us which numbers to exclude?

 

 

 

 

 



Edited by John D on 6/13/2010 at 3:15 PM EST
Ask Your Own Programming Question
Customer reply replied 8 years ago
Hi there, So sorry for my tardy reply, I was getting home....
Anyway...here's what I think...this master metric workbook is linked to three other workbooks which capture data on three different types of clinical research trials. the large numbers whether negative or positive are produced when one date of the formula is missing in the source workbook...then when it gets transfered into the master metrics book, it gets this crazy number because of the source.

So what I'm thinking is that I need to write some type of IF THEN at the source data so the cell that's pulling into the metrics page is 0 if both parts of the formula aren't there....what do you think?

Thanks
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

The formula that I gave you is indeed a type of IF THEN statement, which, unlike the standard IF THEN statement which functions on a single cell, this one acts on the entire range in one go.

 

We can add other conditions to the 'average if' function so that it looks at adjacent columns as well and averages for example all numbers in column A where the corresponding values in column B are not 0. Would you like me to send a sample file?

 

 

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago
Hi there, Yes, I know that it was an IF THEN for a selection of cells as opposed to a single cell...

when I posed my original question to you I was thinking that it would be best to IF THEN the master metrcis sheet, when in fact it may be easier and more accurate to IF THEN the source. Here's an example of what I am saying:

Source followed by preliminary date calcutations
FSFV 15-Apr-10
LSLV 15-May-12
Database Lock 15-Jul-12
Last COV 15-Oct-12

FSFV to LSLV 761
LSLV to Database Lock 61
Database Lock to Last COV 92

As you can see there are dates in all four fields. If however the FSFV has occured but not the LSLV, then we get this:

FSFV 15-Apr-10
LSLV
Database Lock 15-Jul-12
Last COV 15-Oct-12


FSFV to LSLV -38821
LSLV to Database Lock 39643
Database Lock to Last COV 92

Since the calculation can't be completed because one of the necessary points is missing, we get this large negative number.

Then when this prelimiary data is linked to the master metric sheet which is comparing many types if clinical trials for an overall average, this negative number is XXXXX in.

So, if we were to create an IF THEN statement at the preliminary place....to not show the negative number, but to show a zero...Like IF either part of the formula is blank then the cell shows 0, it wouldn't impact the master metric sheet.

Thanks and so sorry to be such a pain on a Sunday Morning....
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

Ok it is becoming more clear now.

 

I need however to have the file so I can set up the formula with proper references to the data. Is it ok to send me that file. If so I will give the instructions for uploading it.

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago
Heck ya.
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

Ok, go to www.wikisend.com and upload it there. You will then get a page that has the download link. Copy the download link and come back here and paste it in your reply.


If the file has sensitive information let me know before you upload it.

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago
Okay, I'll send one of the files, can I then complete it for the rest of the files? Thre are 18 tabs on 3 files that feed into this master metric. So if you complete the necessary steps for one of them, will it be evident enough for me to see it to complete the others?
Thanks

http://wikisend.com/download/534002/FOR JOHN Outsourced_MultiCenter.xls
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

Ok, I am looking at the "Drug01" sheet which is the only sheet that has values, but I do not see the values that you have posted in your previous message. There are only dates in rows 12 to 15 but I don't see any FSFV or LSLV values. Am I missing something here?

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago
This is what I show

Protocol Number 774205
Study Phase 2
Planned number of sites 25
Planned enrollment period (months) 24
Protocol Signature Date 31-Mar-10
Vendor Contract Execution Date
First Site Selected 15-Mar-10
Last Site Selected 16-Apr-10
First Site Initiated 1-May-10
Last Site Initiated 1-Jun-10
FSFV 15-Apr-10
LSLV 15-May-10
Database Lock 15-Jul-12
Last COV 15-Oct-12

And really the dates don't matter, I am getting this ready to deploy....it's the metrics summary sheet where the large numbers show up....
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

Exactly, but my question was where did you get the values that you posted earlier, e.g.

 

FSFV to LSLV 761
LSLV to Database Lock 61
Database Lock to Last COV 92

 

If these values are number of days they certainly are not correct for that data (e.g. LSLV - FSFV = 15-May-2010 less 15-Apr-2010 = 30, not 761)

 

What gives?

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago
The numbers I used before were just in there for examples. If you look at the numbers for what's in there now and how it calculates it's right:

FSFV 15Apr2010 LSLV 15May2010 = 30 days
then LSLV 15May2010 to Database Lock 15Jul2012 = 792

Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

Thanks.

 

Ok, here are the formulas (in the yellow cells) that take care of all possibilities that may lead to either a,negative number or an error message, or a incorrect large result.

 

So if any of the dates are not available or if the second date is earlier that the first date, the formula will return 0

 

FILE

 

Hope this is ok. Let me know if you have any questions, and thank you for clicking Accept if you are satisfied with the work.

 

John D
John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9,666
Experience: Vast experience in Excel programming and business solutions
Verified
John D and 87 other Programming Specialists are ready to help you
Ask your own question now
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

Thanks :)

 

Ask Your Own Programming Question
Was this answer helpful?

How JustAnswer works

step-image
Describe your issueThe assistant will guide you
step-image
Chat 1:1 with a programmerLicensed Experts are available 24/7
step-image
100% satisfaction guaranteeGet all the answers you need
Ask John D Your Own Question
John D
John D
John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9,666
9,666 Satisfied Customers
Experience: Vast experience in Excel programming and business solutions

John D is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed.

One Happy CustomerNew York

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around.

EstherWoodstock, NY

Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know.

RobinElkton, Maryland

< Previous | Next >

Meet the Experts:

ATLPROG

ATLPROG

Computer Software Engineer

7,677 satisfied customers

MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML

LogicPro

LogicPro

Computer Software Engineer

7,175 satisfied customers

Expert in C, C++, Java, DOT NET, Python, HTML, Javascript, Design.

unvadim

unvadim

Computer Software Engineer

1,168 satisfied customers

Good knowledge of OOP principles. 3+ years of programming experience with Java and C++. Sun Certified Java Programmer 5.0.

lifesaver

lifesaver

Computer Software Engineer

950 satisfied customers

Several years of intensive programming and application development experience in various platforms.

ehabtutor

ehabtutor

Computer Software Engineer

936 satisfied customers

Bachelor of computer science, 5+ years experience in software development, software company owner

TheDoctor

TheDoctor

Software Engineer

893 satisfied customers

M.S. in Internet Information Systems

The-PC-Guy

The-PC-Guy

Software Engineer

848 satisfied customers

Extensive Knowledge in PHP, MYSQL, CSS & Javascript

< Previous | Next >

Related Programming Questions
Design a program that will allow a user to Input a list of
Design a program that will allow a user to Input a list of your family members along with their age and state where they reside. Determine and print the average age of your family and print the names … read more
LogicPro
LogicPro
Computer Software Engineer
Bachelor of Technology
7,175 satisfied customers
Write a program that would calculate and display the average
Write a program that would calculate and display the average of an unknown number of positive floating point values entered by a user. The input is terminated by entry of any negative number.… read more
LogicPro
LogicPro
Computer Software Engineer
Bachelor of Technology
7,175 satisfied customers
Assignment 2: Chocolate Delights Candy Company, Part 2 The
Assignment 2: Chocolate Delights Candy Company, Part 2 The Chocolate Delights Candy Company needs to add the following functionality to its cash register: When a customer is checking out, the cash reg… read more
LogicPro
LogicPro
Computer Software Engineer
Bachelor of Technology
7,175 satisfied customers
When a customer is checking out, the cash register needs to
When a customer is checking out, the cash register needs to store the required data pertaining to each item the customer is buying. This data includes the item name, the price per pound, and the numbe… read more
LogicPro
LogicPro
Computer Software Engineer
Bachelor of Technology
7,175 satisfied customers
I need help with this program..... http://www.justanswer.c
I need help with this program..... http://www.justanswer.com/computer-programming/3aw3v-write-program-calculates-prints-average.html… read more
LogicPro
LogicPro
Computer Software Engineer
Bachelor of Technology
7,175 satisfied customers
This project focuses on demonstrating your understanding of
This project focuses on demonstrating your understanding of sequential, selective and repetitive programming statements, methods, and modular programming. Before attempting this project, be sure you h… read more
ATLPROG
ATLPROG
Computer Software Engineer
Master's Degree
7,677 satisfied customers
A program that accepts candy name (for example, chocolate-covered
A program that accepts candy name (for example, "chocolate-covered blueberries"), price per pound, and number of pounds sold in the average month, and displays the item's data only if it s a best-sell… read more
ATLPROG
ATLPROG
Computer Software Engineer
Master's Degree
7,677 satisfied customers
Write a program that would calculate and display the average
Write a program that would calculate and display the average of an unknown number of positive floating point values entered by a user… read more
unvadim
unvadim
Computer Software Engineer
Diplom
1,168 satisfied customers
Visual Logic Programming, pseudocodes and flowcharts
'Note:These all must be done in Visual Logic, I will need an IPO chart, Basic pseudocode(nothing too advance im just beginning) and pictures of the flow charts and a example output sample In a word do… read more
LogicPro
LogicPro
Computer Software Engineer
Bachelor of Technology
7,175 satisfied customers
COMP 122 lab 6 grade array Objectives Use an organized,
COMP 122 lab 6 grade array Objectives: • Use an organized, modular approach to develop programs, including appropriate functions and structure (sequence, selection, repetition and nesting). • Design a… read more
ATLPROG
ATLPROG
Computer Software Engineer
Master's Degree
7,677 satisfied customers
COMP-122 Lab 6 Grade Arrays Objectives Use an organized,
COMP-122 Lab 6 Grade Arrays Objectives: • Use an organized, modular approach to develop programs, including appropriate functions and structure (sequence, selection, repetition and nesting). • Design … read more
ATLPROG
ATLPROG
Computer Software Engineer
Master's Degree
7,677 satisfied customers
I need the following project completed in MS Visual C# XXXXX
I need the following project completed in MS Visual C# XXXXX Edition, of which I need to obtain the completed project so that I may use on my end. Piecework workers are paid by the piece. Workers who … read more
ehabtutor
ehabtutor
Computer Software Engineer
Bachelor's Degree
936 satisfied customers
I need the program to same previous question that was aske
I need the program to same previous question that was asked? about the how many students to process......… read more
LogicPro
LogicPro
Computer Software Engineer
Bachelor of Technology
7,175 satisfied customers
Objectives Use an organized, modular approach to develop
Objectives: • Use an organized, modular approach to develop programs, including appropriate functions and structure (sequence, selection, repetition and nesting). • Design and implement functions that… read more
LogicPro
LogicPro
Computer Software Engineer
Bachelor of Technology
7,175 satisfied customers
I need a code in C++ using Microsoft Visual 2008. Below are
I need a code in C++ using Microsoft Visual 2008. Below are the requirements: Objectives • Use an organized, modular approach to develop programs, including appropriate functions and structure (sequen… read more
lifesaver
lifesaver
Computer Software Engineer
Bachelor's Degree
950 satisfied customers
Objectives Use an organized, modular approach to develop
Objectives: Use an organized, modular approach to develop programs, including appropriate functions and structure (sequence, selection, repetition and nesting). Design and implement functions that pas… read more
Raj
Raj
SAP ABAP Consultant
Master\u0027s Degree
27 satisfied customers
I have a group project due, but everyone else that is in my
I have a group project due, but everyone else that is in my group has bailed on me. It had several parts to it, and each of us is responsible for a part BUT I have to do the entire thing b/c my group … read more
LogicPro
LogicPro
Computer Software Engineer
Bachelor of Technology
7,175 satisfied customers
Part 1. I will be using a dvd collection as the example for
Part 1. I will be using a dvd collection as the example for the following Java program. Create a product class that holds the item number, the name of the product, the number of units in stock, and th… read more
Hirash Riyal
Hirash Riyal
Higher Diploma In IT
174 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x