Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Close Window (X)   
Excel VBA Course
[80% Discount] Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Today!)

Excluding Zeros From Correl Function

How do you exclude zeros from being included in correlation functions?

1 0
2 3
2 0
1 0
3 0
3 3
3 2
2 3


If that was a spreadsheet, I would want to calculate the correlation between the pairs without a zero.

I have tried this formula without success.

=CORREL((IF(D3:D9<>0,C3:C9,"")),(IF(D3:D9<>0,D3:D9,"")))

Thank you for your help


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(80% Discount Ends Soon!)

View Course




Similar Topics







Hi there, I have a string of numbers and I would like to add zeros to the end of it.

For example my numbers are formatted like this: 1234 and I would like to add zeros to the end so it looks like this: 123400 (no decimal).

I would do it manually but I have a column of over 2000 different numbers.

Thanks for your help, you guys are great!


I'm trying to come up with a solution to calculate the man hours and days worked over multiple sheets.

I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.

The days of the week consists of "Name" "Date" Hours"

The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"

Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.

I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.

I have attached a sample file.

Thanks in advance for your help.


is it possible to put two formulasinto one cell?

On the attached timesheet there are columns IN, OUT, IN,OUT
The lunch time is worked out by deducting 'C8 from C9' and this works fine when all 'IN, OUT' cells are filled in but for some reason when just the first two cells are filled in it gives a -12 hour answer.

looking at cell 'C11 ' in the attached worksheet, I currently have the formula
=IF(C13=0,","SUM(C9-C8)) The reason for this is to make it look tidy by having blank cells until a calculation is needed,, My problem is, If someone just comes in for the morning one day for instance 8am to 12 noon then they won't have lunch, but C11 will show -12:00
Can I put another formula into C11 to tell it not to deduct C8 from C9 until a time is put into C9.
The following is the sort of thing (many variations) I have been trying but because I'm hopeless at formulas I'm not having any success
Thanks

=IF(C13=0,","SUM(C9-C8))OR,IF(C9,ISBLANK,"",SUM(C9-C8)


Hi,

Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too.
I need to calculate only the Yes.
I have tried using the filters in the Pivot Table field list and they are not working.
So now i'm trying to add a COUNTIF formula in the Calculated Fields section.
The formula I'm using is =Countif('Time limit extension'="yes") and i get the answer "too few."
What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me.

Please HELP!!!


I have cells in a column, some colored yellow, some not. I am trying to use
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?

=SUMIF(D3:D13,"criteria",D3:D13)

I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)

It might just be that I don't know what this means in Help:

"color" --> 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).

Can anyone help?



I have a spreadsheet set up with an employees information, including the start date. After each year that the employee is working, he gets bumped up on the pay scale. For this reason, I only need to know how many years the employee has been working, rounded down to the nearest year.

This is what I have so far (hire date is in column B):

=((TODAY()-B4)/365)&" YEARS"

This function gives me a number with many decimal places.

I tried:

=ROUNDDOWN((TODAY()-B4)/365),0)&" YEARS"

It tells me I have too few arguments. Please help!


I am trying to figure out how to write a formula to figure out production cycle time.
We will be building 8 "widgets" a day.
We will work 10 hours a day.
There is a 20 minute break at 9:20 am.
Production shuts down for lunch 30 minutes for lunch at 12:30.
Production starts at 6:00 am.
Here is what I can do.
Production cycle time = (10*60)-30/8 or 71.25 minutes
If we start at 6:00 am, the first scheduled cycle completion time = TIME(6,0,0+71.25/1440)

My problem is when I am trying to use a logical "IF' statement to account for the 20 minute break or lunch and still calculate the end of each cycle time through the day I receive a number of error messages.
This is the formula I am trying to make work. I have the cell the formula is in, formatted with a "mm,ss" format.
=(IF(D4+G1/1440>9:20,(D4+G1)/24,(D4+G1+15)/1440))
I have included an attachment to help (a picture is worth a thousand words).

Any help will be greatly appreciated.
I have bought a number of books in an attempt to figure this out, and I am still stumped


Hello,

I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:

I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.

I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?

If somebody could help me out that would be great.

Thanks,
Randy


I have a pivot chart with a bunch of data series, and every time we
include/exclude one of the series to see how the chart changes, all the
colors change and we have to spend time re-identifying which series is
which, using the legend to check the colors.

Is there a way to "lock" the colors, so that the bars stay the same colors
regardless of whether other bars are included/excluded?

Thanks!
Keith





I often use if statements to return empty cells, for example:

=IF(a1=0,"ERROR","")

The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents.

Is there a way to designate, in such a formula as above, to return a TRULY empty cell?

Thanks


Is there an Excel guru that can help with this - its related to "drop down menus"


I have 2 colombs of data.

AT the bottom of the first, I have created a drop down menu using the "data validation" feature in excel.

At the bottom of the second colomb, I have used an "IF" function that returns a result, which depends on what value is chosen from the drop down menu in colomb 1

The problem I have is that I want the TRUE result from the IF function to be another drop down menu, being the data in colomb 2.


How do you write an IF function where the TRUE result is a drop down menu??
I tried to create a drop down menu of colomb2 elsewhere in the spread sheet, and used that cells location as the TRUE value, but this didn't work either...

ANy suggestions??


I have searched and read all the help files. I find the properties of
an object, I see how I can "lock", "size and move with cells" or "not
move with cells". No matter what I select, the object moves off the
screen, when the user, scrolls to the right of the spreadsheet.

Is there a way to lock the position, let's say , in the upper right
corner and have it stay there?

This would be quite useful for an EXIT button, that I have created,
that will close the program without saving (it's a read-only file.)

Thanks to all the wonderful people here that have been so helpful and
give us their valuable insight and time.

Jo




I get the error message "reference is not valid" each time I open my spreadsheet. I get this message three times, and once I am done clicking ok on all three of them, my spreadsheet works just fine. The problem is, I have to send it to a bunch people.

I have a sheet with raw data a sheet with pivot tables a sheet with a dashboard and a simple macro
I don't have any #REF cells either.

Someone has any idea of what could be the problem?

thanks

NA


I need to create a IF statement where it will count the character length and if not 6 then add leading zeros to field to make a fixed 6 character length .. so if field is 489 then the if statment would make it 000489


Hi, hope someone can assist, i want to add some arrow syimbols in to a formula, so that when the formula works out it shows an up arrow for higher or a down arrow for lower, i am using this formula at the moment because i dont know any better but it works, putting in the word up or down:

I have entered the following formula in to a cell: =IF(S3<R3,"DOWN","UP") it works however i want to replace the word Down and up with the relevant arrows symbol. How do i do this?

Thanks


How do I set the formula if

if F1...I1 <10 = Pass, >=10 Fail (and highlighted red), and any cell between F1....I1 is empty = blank


Good afternoon!

I'm working with Excel 2003 sp 3 on Windows xp.

I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker:

=INT(Q3/8)+(((Q3/8)-(INT(Q3/8)))*0.8)

That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example:

weekending 12/25 = 16.2 which is 16 man days and 2 hours
weekending 1/1 = 24.7 which is 24 man days and 7 hours

Total = 41 man days and 1 hour

I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or "pushing a button".

Any advice is greatly appreciated!

Happy New Year!

~ Brenda ~


I am using the following formula to calculate the number of days between a date and the current date: =DATEDIF(A1,TODAY(),"d") What do I add to the formula to leave cell blank if no date is entered in the cell for calculation.


I have a given length of steel bar...Example(550 feet)
I have various cut lengths that I can pick from...
14'-10".....13'- 9".. 12'-8"........(up to 6 different lengths)

I need to know the best combination of lengths to cut the long bar into to have minimal scrap left when we get to the end of the 550 feet.....

Cut as many of the longest cut lengths possiable.....Cut as few of the shortest cut lengths possiable

Due to the fact that I will have to convert this to a PLC application when completed, I want to find a mathmatical way to do this inside excel and not use the "solver"....Assuming that I/we can come up with an equation to do this .....
Any and all advice/help would be appricated.....


In Excel there is a difference between cells that are blank (= "") and that are empty. You can make blank cells empty by selecting them and pressing the delete key, but I need a fast way to do this on all blank cells in a large worksheet. The only way I have found is with a VBA macro that loops through every cell, tests for '.Value = "" ' and then uses the '.Clear()' function, but doing this on 30 columns x 10000 rows is far too slow. Any solutions?

(The reason I need to do this is for importing into Access, the database treats empty cells as NULL which is what I want. Blank (but not empty) cells screw the import process up.)