ExcelBanter - Excel Worksheet Functions

Varying cell ranges in formulas according to number of cells with certain value

Dranoeler — Mon, 20 May 2013 04:10:29 GMT

Hi all, I've been trying all morning to figure out a complex problem, and I was hoping the good people here would be able to help.

So I have a spreadsheet with a list of companies, list of users within those companies, and the status of those users(Active/Inactive/Deleted). I'm trying to determine the company-level status based on how the overall status of all the users in the company.

E.g.
A B C D
Company A User 1 Active
Company A User 2 Active
Company A User 3 Inactive
Company A User 4 Deleted
Company B User 1 Inactive
Company B User 2 Active
Company B User 3 Inactive

In the above example, cells D1 to D4 would list "Active", since Active users form the bulk of the company. D4 to D6 would list "Inactive" for Company B.

I've figured out the first half of what I need to do:
=IF(COUNTIF(A1:A4,"Active")>COUNTA(A1:A4)/2),"Active",IF(COUNTIF(A1:A4,"Inactive")>(COUNTA(A 1:A4)/2),"Inactive","Deleted"))

My problem is in getting Excel to automatically define the cell ranges according to the cells that contain "Company A", "Company B", etc. I have over 5,000 rows on the spreadsheet so having to manually change the cell ranges for each company would take forever.

I will be EXTREMELY grateful if anyone could help!!

Also posted on http://www.excelforum.com/excel-gene...39#post3245639, and http://www.mrexcel.com/forum/excel-q...ain-value.html

Automatically populate value of cell dependent on adajecent cell

Crystal84 — Sat, 18 May 2013 02:46:32 GMT

I'm not sure if I need a formula or a script for this, I've looked at almost every formula in excel and I can't figure it out! Here's what I'm trying to do:
In cells E3:E29 I have a drop down menu with a list of services. Depending on what the selection is in those cells, I would like the next cells F3:F29 to automatically populate with a value. For example in E3 "Lesson Mozart" would be selected, so F3 would automatically have $70.00. Then E4 "Training ride" would be selected so F4 would automatically have $40.00. In addition, I would like the G column cells to have a value dependent upon the E or F values, but I image when I figure out the first part of the problem, I get the next part! Thank you again!

Formula to add two spaces into a number stored as text

michael@riversway.net — Fri, 17 May 2013 11:20:55 GMT

Hello. I'm trying to do as the title says. The numbers must be arranged "0000 000 0000" and I'm trying to get that format applied to all the numbers in a column. The only problem is there are about 3000 numbers in that column so I can't do it manually.

From searching around I've found the following formula =CONCATENATE(LEFT(H2,4)," ",RIGHT(H2,3))

This seems to be what I'm looking for but I'm having trouble adapting it for my purposes. Anyone who can help me out here?

Generate series according to range and increment

primeride2k — Thu, 16 May 2013 17:21:31 GMT

I am working with a two page workbook. On page one I have upper and lower values of a range (example 0.0 to 5.0, or 0 to 100) and an increment value (example 0.2 or 5). On page two I would like a column to automatically fill with the series of values within the range according to the increment value.

For example: If the range on page one is from 0.0 to 5.0, and the increment is 0.2, I would like a column in page two to fill 0.0, 0.2, 0.4, 0.6, 0.8, 1.0, 1.2� �4.8, 5.0 in a column from top to bottom.

I hope this is clear enough; I am not very versed in Excel vocabulary. Any help is appreciated. Thanks!

Dynamic Ranges Adding Extra Row

KeriM — Thu, 16 May 2013 17:16:30 GMT

I'm trying to set up dynamic ranges (with mixed text/numeric data) using the following base formula:

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

I have a header in the first row, so I want to start my range an A2. When I change the first reference to $A$2; however, the range goes one more row than it needs to. I've also tried to set the row offset to 1 and it still adds the extra row. Does anyone have any suggestions? Thanks!

CONDITIONAL FORMATTING IN EXCEL 2010

Malcolm Austin — Thu, 16 May 2013 11:08:23 GMT

Sorry, this is obviously a really simply one, but the help comments are not
making sense to me!

I wish to format A1 (background colour) when its value is higher than B1.

As in:-

A1 = 3.3% B1 = 2.8% so cell A1 background colour will change.

Thanks for the help!

Malcolm..

Text Dates/times to d:hh:mm:ss

Bigskenney — Wed, 15 May 2013 18:57:51 GMT

I have a problem that if able to use software that I am more accustom to, like MatLab, I would be able to fix fairly easily. Unfortunately I am only able to use Excel for this and I am still learning the language and capabilities of Excel. I have a text field that is representative of outage time that is filled with items like this: 1day 9hrs 35mins 53seconds, 15hrs 20mins 50seconds, and 11days 23hrs 59mins 14seconds. I have been able to use Substitute to put this in a ':' for the string portions, 1:9:35:53, But this is still not able to be sorted longest to shortest because if there is no 'day' to substitute it is left blank, 15:20:50. I have tried formating, which didn't help, and I can't find a way to use an ELSE statement to substitute a '00' if the 'day'/'days' isn't present. I would think that Excel is able to do something as simple as this, I just am missing the proper language to get it done. Thanks for the help.

Comparing two lists

RB[_2_] — Mon, 13 May 2013 14:57:27 GMT

Hello,

I have to compare two list and no vlookup does not work on this:
On list 1, I have the names of people and cities that they visited.

Eg. James New York
Dallas
Houston
Seattle
Mary Phoenix
San Francisco
Austin
Portland

List 2 has the names of the same people but not all the names of the cities they visited

Eg. James New York
Dallas

Mary Austin
Portland

So I know that james and Mary visited other cities but they aren't listed on list 2

I need to compare the two lists and identify on list 2 the names of the cities that are on list 1 that they visited

Thanks!

Multiple VLOOKUP lookup values

SkyTheImmense — Sun, 12 May 2013 22:33:11 GMT

Edit: Believe it or not I worked it out. Thanks anyway.

Removing #N/A from an "If/Then" statement

Collin Ulvund — Thu, 09 May 2013 14:53:32 GMT

I'm doing some report making for my job, and pieced together this formula:

=IF(D9=0,NA(),D9) with base formula of =IF(hostcell=0,NA(),hostcell)

The formula has worked wonderfully, but it leaves "#N/A" in the cell and my supervisor would like to not see anything within the table, as they are being included in this report.

Does anyone know how this could be achieved?

Rosters, Dates and Displays

Christian Michael — Thu, 09 May 2013 11:53:36 GMT

Goal
Show in one cell the surnames of band members (all in different cells on a roster page) as members of a particular band (which maintains the same name but changes members based on quarter/monthly), based on both the band name and when a particular show date is.

i.e.
Feb to Jun, Awsum Nuggets members were Johnson, Willis and Tart.
Jul to Dec, Awsum Nuggets members were Harper, Hopper and Jackson.
Awsum Nuggets played a show Feb 2, 2013.
Based on the show date and Band name (as log contains names of other bands that also change qtr/mo, cell should show names according to roster based on when band was staffed by which members.

Log
Show Date | Band | Members (this cell has formula to display member names)

Roster (feeding info)
Arrive | Depart | Band | Mbr1 | Mbr2 | Mbr3 | Mbr4

Current Formula (not working)
=IF(and(($A6:A12=MEDIAN(Roster!A6:B6,)),B6:B12=Ros ter!C6)),Roster!D6:K6,"")
Translation
=if (date in column A is between A6:B6, AND the name in Roster!$C6 is in column B) then display member names from the associated band or ""

Any ideas?

Nameing a function

Leslie Charles — Wed, 08 May 2013 22:01:51 GMT

Excel 2010

Suppose I have the formula =Degree(A1) in cell B1.

In cell C1 I write a formula using the formula in B1 as one of the
parameters.

I think I understand how to name the formula in B1. Chooseing Ctrl + F3 seems to do the trick. But when I try to auto-fill my formula in C1 down
the column it doesn't seem to work correctly.

Any help appreciated.

Converting a number into a cell reference

szwanger — Wed, 08 May 2013 16:00:05 GMT

I want to pull a number from a particular cell and then use that to create a cell reference for the cell in row Q and the column of that number, and look up that cell in a different sheet.

For example, let's say Sheet 1, cell K1 contains the number 72. I want =formula to return the contents of Sheet 2, cell Q72.

So how do I write this formula? :-)

Index Match & #values

DanielF — Wed, 08 May 2013 12:16:40 GMT

I am using an Index function, together with MATCH. The index refers to a separate workbook. The problem occurs when the INDEX imports data from an empty cell.

From one sheet it simply displayed nothing (this is what I need).
When I've added an Index to this Index (meaning... INDEX function + another INDEX function) it returned #VALUE where the second INDEX encountered a blank cell.

Is there anything I can do to overcome this issue? Maybe something to ignore the particular INDEX function if it returns a #VALUE result?

Thanks a lot,
Dan

SUMIF with criteria "<>" & "="

MikeG[_2_] — Tue, 07 May 2013 09:38:09 GMT

I'm using excel 2007. I have 2 columns of data. A1:A8 contain various numbers only. B1:B8 contain numbers, text, & empty cells. I use the formula SUMIF(B1:B8,"<>",A1:A8). This works fine for my needs as I'm trying to sum the numbers in column A that have corresponding numbers or text in column B but not corresponding blank cells.
If instead I replace the criteria "<>" with "=" and use the formula SUMIF(B1:B8,"=",A1:A8), I sum the numbers in column A that have corresponding blank cells. This also works with COUNTIF(A1:A8,"<>") and COUNTIF(A1:A8,"=") by either eliminating blank cells with "<>" or eliminating numbers and text with "=", thus giving the same results as COUNTA(B1:B8) or COUNTBLANK(B1:B8) respectively would.
I cannot find any built in substitutions for the =SUMIF example that I brought up first that would achieve similar results as COUNTA & COUNTBLANK do..
Why does "<>" disregard the blank cells and "=" disregard the numbers and text?