Select Multiple Cell Ranges In "sumif" Formula?

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 created an excel spreadsheet to track attendance of an exercise class I am teaching. I would like to know each person's current percent attendance. (so if we have had 5 classes and they have attended 4 of those classes then it should show 80%). I would like it to keep a running tab, so as I add in the person's attendace it will continue that count, up to the 26 classes that are being offered. I thought if I did a sumif/countif I could get it to work, but it doesn't. Here is what I tried: =sumif($e$4:$ad:4,">0")/countif($e$4:$ad$4,">1"). Do you have a suggestion that would work? Thanks!

I have a workbook that has recently changed on me and is causing a lot of grief. I have a lot of named ranges that the scope of the named range has changed to a new sheet. This is creating a lot of problems! Is there any way to manually change the scope of a named range? without deleting the nmaed range and recreating it?

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 ~

Hello everyone, I am new to this forum and have been using excel for about 3 years. I have learned alot but still can not figure out how to make numbers add up in one box like a running total. Meaning, If I have a list and in the list is one cell for each item say like cell a1 is for a can of coke and cell a2 is for a car tire and so on,and i want to keep a running total by adding a number to cell a1 or a2 like say "3 i.e. 3 tires or cans of coke" and later that day i come back and need to add 2 more ......so i want to be able to just click on the cell a1 or a2 and enter the number 2 and the cell would add the number in it to the number im adding to it to show a result of 5 tires or whatever. I am sorry If i am not asking the question in an understandable manor but its the best way I can think to ask it.lol anyways, any help would be appreciated, Thanks in advance.


James

From limited experience I know that excel calculates dates via serial numbers.

I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)

I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.

What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?

Hopefully a simpler question for your experience level than mine.

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

Great tip! But one thing: On my machine (Excel 2008 Mac), the values in the formula cell are not correctly calculated simply by dragging. Instead, the values are equal to the cell above (the first entry formula value). To get the correct value, I need to click in the formula bar and then hit enter. (I only discovered this after an hour of tinkering, figuring I had botched the formula!)

Are there any ways around this so that it updates upon dragging the formula?

Hello from sunny Madrid

I have created a complex formula to help me calculating Golf handicaps for players.


The formula is in the first cell and I want to drag the formula across to the right to cover the 18 holes.

But ALL the values change and I want some to change and some to stay constant.



How do I tell excel to drag the formula across but keep certain parts of the formula the same..ie ....=D11.... in the formula when dragged across doesn't go D12,D13;D14 in each cells formula and should stay as D11 for all.

Many thanks for your help

Ole

Today I ran into an odd problem. I typed in values for column A rows 1 through 10 then values for column B rows 1 through 10. then in column C, I made the formula C1=A1/B1. The math was correct it showed 542 in the C1 cell. So I dragged that formula down and it showed 542 in all column C cells which is not correct. And when I went to check to see if the formula was correctly dragged it was. For instance, the formula in C2 is =A2/B2 however the value of that cell showed 542 which was not the correct math/value.

But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.

This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.

Thanks for any tips on this.

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

hi,

I would like a formula that would list all the items in row B that match the criteria in row A. the first cell with formula would list the first item, the second cell with the formula would list the next item, and so forth. Also, column B might have a duplicates that should be listed. Is this possible? I cannot manipulate the order of the original items (ie, filters) because this data is being used to derive other formulas.

Hi there,

I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!

In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:

=Sheet1!A3 (or whichever cell it is)

That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).

If anyone could point me in the right direction, I'd be very very grateful. Thanks.

Hi Everyone,
I have searched online and in help but can't seem to find the best solution...

I have values like 00904BB303D6 that need to become: 00:90:4B:B3:03:D6 (the value is always 12 digits, and the : needs to appear every two digits.) Is there a formula that can easily do this for me? Your suggestions are greatly appreciated!

Thanks in advance!!!!

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.