Nested if statements Arrays - help!!

I basically have a collum of dates and I want to have a descriptive
cell with the range of dates. The dates are all entered from another
sheet by a user and this page is a summary page the formula looks
something like:

=A25&"-"&IF(AND(A26=0,A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A34=0,A3
5=0,A36=0,A37=0),A25,IF(AND(A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3
4=0,A35=0,A36=0,A37=0),A26,IF(AND(A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3
4=0,A35=0,A36=0,A37=0),A27

with A25 being the first date and the sequence of IF statements
determining what the end date should be. This works fine untill the 7th
If statement, because I guess that is the maximum nested ifs one can
have. It seems using an array will help with this but I am lost. Any
help is appreciated.

--
0
Dan2182 (3)
11/24/2003 3:49:33 AM
excel 39879 articles. 2 followers. Follow

4 Replies
450 Views

Similar Articles

[PageSpeed] 27

If all the cells contain a date or a zero, the following formula should 
work:

  =TEXT(A25,"mmm dd, yyyy") &" - "& TEXT(MAX($A$26:$A$39),"mmm dd, yyyy")

Diver Dan wrote:
> I basically have a collum of dates and I want to have a descriptive
> cell with the range of dates. The dates are all entered from another
> sheet by a user and this page is a summary page the formula looks
> something like:
> 
> =A25&"-"&IF(AND(A26=0,A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A34=0,A3
> 5=0,A36=0,A37=0),A25,IF(AND(A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3
> 4=0,A35=0,A36=0,A37=0),A26,IF(AND(A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3
> 4=0,A35=0,A36=0,A37=0),A27
> 
> with A25 being the first date and the sequence of IF statements
> determining what the end date should be. This works fine untill the 7th
> If statement, because I guess that is the maximum nested ifs one can
> have. It seems using an array will help with this but I am lost. Any
> help is appreciated.

-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/24/2003 5:12:38 AM
In article <3FC19346.5020003@contexturesXSPAM.com>, Debra Dalgleish
<dsd@contexturesXSPAM.com> wrote:

> =TEXT(A25,"mmm dd, yyyy") &" - "& TEXT(MAX($A$26:$A$39),"mmm dd, yyyy")


This did it-thank you!!! Just one question --- why the $A$26 or really
why the $ in the formula. I looked up max - I should have done this
before but I am still not sure ..... Again much thanks!!!!!!
0
Dan2182 (3)
11/24/2003 3:17:00 PM
You're welcome.

The $ character makes it an absolute reference. If you copy the formula 
to another cell, it will continue to point to the range A26:A39 (For 
consistency, I should have used $A$25 in the first part of the formula.)

There's information about absolute vs. relative references in Excel's 
Help files, and a short tutorial here:

 
http://www.jmu.edu/computing/tutorials/microsoft/excel/move/creating.shtml

Diver Dan wrote:
> In article <3FC19346.5020003@contexturesXSPAM.com>, Debra Dalgleish
> <dsd@contexturesXSPAM.com> wrote:
> 
> 
>>=TEXT(A25,"mmm dd, yyyy") &" - "& TEXT(MAX($A$26:$A$39),"mmm dd, yyyy")
> 
> 
> 
> This did it-thank you!!! Just one question --- why the $A$26 or really
> why the $ in the formula. I looked up max - I should have done this
> before but I am still not sure ..... Again much thanks!!!!!!


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/24/2003 4:50:58 PM
In article <3FC19346.5020003@contexturesXSPAM.com>, Debra Dalgleish
<dsd@contexturesXSPAM.com> wrote:
This works great thank you , thank you!!!! One question why the $ in
the Max arguements?


> If all the cells contain a date or a zero, the following formula should 
> work:
> 
>   =TEXT(A25,"mmm dd, yyyy") &" - "& TEXT(MAX($A$26:$A$39),"mmm dd, yyyy")
> 
> Diver Dan wrote:
> > I basically have a collum of dates and I want to have a descriptive
> > cell with the range of dates. The dates are all entered from another
> > sheet by a user and this page is a summary page the formula looks
> > something like:
> > 
> > =A25&"-"&IF(AND(A26=0,A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A34=0,A3
> > 5=0,A36=0,A37=0),A25,IF(AND(A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3
> > 4=0,A35=0,A36=0,A37=0),A26,IF(AND(A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3
> > 4=0,A35=0,A36=0,A37=0),A27
> > 
> > with A25 being the first date and the sequence of IF statements
> > determining what the end date should be. This works fine untill the 7th
> > If statement, because I guess that is the maximum nested ifs one can
> > have. It seems using an array will help with this but I am lost. Any
> > help is appreciated.
0
Dan2182 (3)
11/25/2003 1:53:38 AM
Reply:

Similar Artilces:

Please Help!!! Macro required to select new record in Subform
Hi all, I have Forms in my database like (see below) MainForm SubformA in MainForm SubformB in SubformA Command Button on SubformA I have below macro on command button Private Sub Command1_Click() Me.SubB.SetFocus DoCmd.GoToRecord , , acNewRec End Sub When I open Form "SubformA and click button which is on "SubformA, macro works fine and it takes me to new record on "SubformB" which is in "SubformA. But when I open "MainForm" and click button of "SubformA" then macro don=92t work. I want to open "MainForm" which ...

Problem with array and CComboBox control
I have a header file in which is contained the following array. const char *directionVal[][24] = { { "Left-to-right", "ltr" }, { "Right-to-left", "rtl" }, { "Left-to-right Override", "ltr-override" }, { "Right-to-left Override", "rtl-override" } }; In OnInitDialog() I load my combo box control with the strings from the array like so. for(int d = 0; d < sizeof(directionVal)/sizeof(directionVal[0]); d++) m_cDirectionCombo.AddString(directionVal[0][d] ); I want to show the first s...

arrays
I have an array with about 1500 names in it. How can I add those names to column A1 on sheet1 and go down one cell 1500 times and add the next value to the spreadsheet? in Excel07 VBA apologize for duplicate post, seem to have connectivity issues "Striker3070" <striker3070@qwest.net> wrote in message news:F0806BFF-7975-4C06-A448-37FFDBEB9DE4@microsoft.com... > I have an array with about 1500 names in it. How can I add those names to > column A1 on sheet1 and go down one cell 1500 times and add the next value > to the spreadsheet? in Excel07 VBA ...

Calculator has vanished! Help!
Yesterday, I went to use the caluclator on accessories menu to find it had vanished. I have done numerous searches for it and am baffled why it happened? Any suggestions on how I can get it back on to my accessories menu? Do you see any drop down arrows at the bottom of your menu on the accessories menu? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ----...

Help... Hacked exchange 2k3???
I just posted in the SBS2k3 but thought an expert here may be able to help better: I have been trying to find why my sbs 2k3 reporting has been saying my users are sending 1000's of email that they aren't and found that something is in my exchange doing some strange stuff... It seems that in my exchange log files this virus or hacker is forwarding using some sort of code? This is what I was able to get out of it.. It is using the first administrative group? Anyone seen this? Any ideas?? Thanks in advance, J 2/18/2004 0:15:12 GMT - - - CFC - /O=CYPRESS/OU=FIRST ADMINISTRATI...

Help: displayed characters all changed to graphic boxes!
I ran repair on windows live essentials. I did not back up my system and apparently the system did not make a restore point. I was instructed to reboot. The welcome screen and other screens were normal except that every non-space character is represented as a graphic box. Boxes represent all places where characters can be typed in response boxes. Typed responses appear to work. Canned messages are displayed normally. Composed messages are one box for every non-space character. This affects every application both user and system. I have been trying to figure out what has happened ...

Visual Studio 2005 Setup Project help
Can anyone tell me if it is possible (and how?!) to do 2 extra things using the VS2005 Setup Project: I want to add an 'uninstall' option into the start menu folder. I want to add a "launch application" option on the last page of the installation. TIA ...

Counting values in arrays
How do I get from a vertical single-field array to an array of the sam size, each original value converted to a count of that value in th original array? E.g., 1 -> 3 0 -> 2 1 -> 3 0 -> 2 1 -> 3 2 -> 1 I start with a calculated array, not a cell range, so (I'm assuming {=COUNTIF(Array,Array)} won't work. Steve Przyborski Boston, Mass -- Message posted from http://www.ExcelForum.com Two methods using an array formula and countif Assuming that the data starts in A13. Type the following and drag down - the range w...

Pleasee 0x800CCCOE Help Me
I CANNOT USE OR OPERATE MY OUTLOOK EXPRESS< AT ALL!!! ENCLOSED IS A FORM I FOUND ON IT< BUT NOTHING HAS WORKED THAT IT SAYS TO DO< PLUS I HAVE WINDOWS XP< WHICH THIS DOESN'T SUPPORT. I CANNOT SEND EVEN OFF THE INTERNET, A PAGE. iT'S BEEN 1 1/2 WEEKS THAT I HAVE BEEN TRYING TO FIND A ANSWER, EVERY DAY, DAY IN AND DAY OUT/ i HAVE CALLED THE PHONE COMPANY, HP PAVILION, THE PROBLEM LIES WITH OUTLOOK AND THIS, READ BELOW. tHANK YOU FOR YOUR TIME, ON REASEARCHING THIS. DENISE GASPER OLEXP: Unable to Send or Receive Messages With Dr. Solomon's NetGuard Installed V...

Help! Cannot delete an orphaned merge push subscription
I have inherited a subscription to a remote database that is no longer required. Turns out the remote server has been decommisioned but I still cannot delete the local subscription in SSMS. When I try to delete it, I get - "Invalid object name sysmergearticles." How can I make this orphaned subscription disappear from SSMS? ...

Formula Help? #4
I am not that good with Excel so please bear with me..... I have a column which lists prices quoted and another column which lists the actual price. What I have to do is figure out a formula that will compare column 1 (price quoted) with column 2 (actual price) and if column 1 is 5% or more lower than column 2 I need it to identify it ie maybe turn it red or something Column 1 Column 2 Quoted Actual �100 �120 �200 �250 ! �300 �300 �400 �550 ! I simply have no idea how to do this and am struggling as I do not underst...

Append Query help 08-20-07
I am trying to do an append query on the fly. I have several different types of textboxes, check boxes, and combo boxes as input. The ones that I seem to have trouble with are the memo fields or combo boxes. Below is the SQL code that I have printed out to help debug. INSERT INTO TBL_Map (MapID, submit_type, Push_to_Mainframe, file_format, fields_ignore, logic, map_status, Business_Need, PPED_map, Comments_map, transfer_date, test_file, revision_change) VALUES (NewMap,FTP,H244ftp, delimited,none,none,New Request,Technology Issue,-1,none,,none,none); In the immediate window, I hover over t...

VBA Help Files
Need to find out where I can download C:\Program Files\Common Files\Microsoft Shared\VBA\VEenU13.HLP they are missing from Help menu entirely. TX JHAM Hi they should be on your Office installation CD -- Regards Frank Kabel Frankfurt, Germany "JHAM" <Hamilton3576@bellsouth.net> schrieb im Newsbeitrag news:719b01c483c1$bb9ef4a0$a401280a@phx.gbl... > Need to find out where I can download C:\Program > Files\Common Files\Microsoft Shared\VBA\VEenU13.HLP they > are missing from Help menu entirely. TX JHAM VEENUI3.HLP is an Excel 97 help file. If I recommember properl...

Urgent , How to search data. pls help
Hi , I have two sheets in a file,Sheet1.Datalist, Sheet2.Mapping. Sheet Datalist contains two column. Itemcode, Rate. Profit ------------------------------ AAAA 12.50 CCCC 4.50 DDDD 11.50 EEEEE 12.75 FFFFF 09.25 Sheet Mapping contain Code Profit -------------------- CCCC 2.50 DDDD 12.30 XXXX 10.50 FFFF 09.25 AAAA 00.25 I have to find each code of datalist in mapping sheet and if found there , the corresponding profit should be written in datalist sheet on the profit column. ...

Dave, Gordan, someone help!
Sheet 1 Index Doctor Month Year Date Production 1 Whitney Jan 2004 1/31/2004 21000 2 Whitney Feb 2004 2/28/2004 22000 3 Chong Jan 2004 1/31/2004 18000 4 Whitney Mar 2004 3/30/2004 20000 Sheet 2 With a drop down box I would like to be able to choose the (year and doctor) which would fill in the correct production info in column b A B Month Production Jan xxxxx Feb xxxxx Mar etc. Apr May Hope this is plenty of info. I know how to make it work if it just had to match one variable but since I want to match two things I'm stuck....

IIf Function help with my Query
First some background, I have two tables called 2007 Grid and 2008 Grid, in these tables I have a field called Census Rec'd. For my query, I need to create a field called Census Rec'd and I need this field to do the following - If "Plan Year" ends in 2007 from "AFTAP Table" then insert Census Rec'd from the 2007 Grid and/or if "Plan Year" ends in 2008 from AFTAP Table then insert Census Rec'd from the 2008 Grid. Any suggestions? I suggest you change your database design. Having a table for each year is incorrect. You are starting to se...

Exchange/Domain migration help needed.
Hi there, The company I work for has recently gone through administration and has now come under new ownership. Due to this the name of our company will more than likely be changing (although this hasn't been confirmed yet) and I know I will be called upon to sort out the AD and Exchange side of things to reflect the new company name. The problem is, it's not something I've done before and guess that as it will involve changing the domain name and exchange organisation, I'm guessing it will be easier to setup a new domain, migrate all objects over and then once everything is ...

Formula help #15
Hi All, im tearing my hair out here, seems so simple but i cant get my hea around it!!!! for example i want the following to calculate i have a part number, then a quantity (both in different cells) but in the third cell i want to calculate if the quantity of part pml2 i less than 5 then there is one price but if more than 5 another pric and if more than 10, yet another price! I have some 200 parts to calculate like this & its driving me NUTS t work out the formula!!!!! Hope ive explained this ok!, and if anyone can help it would be greatl appreciated! Many Thanks & Kind Regards...

Re-Order Function??? Please help
I have a serious issue that I can not seem to resolve. I wish I could post the actual spreadsheet as it is hard to explain....but here is my best attempt I need to recreate this summary page Jun Jul Aug Sep First Order 51 18 19 9 First Re-Order 0 34 20 11 Total Ordering 51 52 64 55 Store Front Distribution10.2% 10.4% 12.9% 11.0% Total Depletions 106 73 198 92 Average Case Per Store2.1 1.4 3.1 1.7 My data is as follows: (account names and order history by month...see below). The total depletions, and average deps are not the problem....that is easy....

Help with { character entry in a formula
I have a brain lock so I can't even do a search I need the key strokes and terminology for this formula entry. I know it takes a special key stroke entry but I can't remember it to fix the formula Can someone help an old guy remember? thanks {=INDEX(Data!$D$2:$D$121,MATCH(1,($A$3=Data!$A$2:$A$121)*($A7=Data!$C$2:$C$121),0))} Click inside the formula bar, press CTRL+SHIFT+ENTER (CSE) to confirm the formula. Excel will insert the curlies: { }. If you don't see the curlies (I'd always do this visual check), re-do the CSE confirmation. Joy? thump the YES b...

Macro help!!!
Hi, i need a macro that will save the workbook as html and insert an hyperlink in page and a script for password. I have the script, but i don't know how to make the macro to insert the code in the body of the html. Can this be done? Thanks! ...

Help!!!!!....Too Few Parameters
Hello Everyone/Anyone...first time posting...after having gained valuable tips from others' posts. I didn't know which board (Access/Excel) I should have posted this query (no pun intended) but here goes....This is a step by step account of my pridicament: In Access: Step 1---I have 2 linked PRN files in a database with identical field names. Step 2---I create a single named query dumping all fields from both linked files (via SELECT/UNION statements) with additional field within the query that identifies my records from each linked file. Step 3---Query runs great, I get results...

Combination to Two formulas with the Help of AND / OR / IF Functions
Hi, This is Akash Maheshwari, a frequent user of your group. I had querry which i m not able to break through since last few days: I have two Formulas: =IF(D7="V Top 9702045619",AccountWiseReport!K5-F7+I7-J7+K7-G7-H7) =IF(D7="V Top 9702043906",AccountWiseReport!K40-F7+I7-J7+K7-G7-H7) What i want is if i select "V Top 9702045619" in the cell D7 the first formula would run and viceversa if i select "V Top 9702043906" the second formula should run. I want to combine the above mentioned two formulas in such a way that i get the output in one c...

POP3 support or mail account configuration help request
I have hot mail account like ''dhulashia@hotmail.com" want to set with windows mail, os=Vista, what is the detail proceess to set it. manoj dhulashia To retrieve your Live/Hotmail with Windows Mail, you have to access the POP3 server (mail is downloaded to your computer). the required settings are here... try 587 for the Outgoing port... http://mailcall.spaces.live.com/Blog/cns!CC9301187A51FE33!49799.entry With the newer Windows Live Mail, you can access the Hotmail/Live account via DeltaSync (mail is kept on the server). -- Dave N. MS-MVP (Mail) Win...

Constant Outlook 2003 file error help please...
"Unable to display the folder. Microsoft Office Outlook could not access the specified folder location." Is the message I get every 60-90 minutes when Outlook 2003 is running (on XP SP3). If I close it, its not really closed and I have to CTRL-ALT-DEL to finally stop it. Usually when I restart it, it will return to normal but run thru a mailbox check to see if files are ok. Then after another 60-90 minutes, poof, same error again and I have to do the same again. The calendar does the same thing. Any ideas what's going wrong? Fix?? Do you have multiple PST files op...