Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This should be easy...
But for some reason I can't get it to work. Here's the spreadsheet
setup: I've got three columns of data that each describe a single transaction during separate stages. I've got another column that I'd like to use for determining the most up-to-date stage total. So, I came up with the following formula: IF((AND(A1<0,B1<0,C1<0)),C1,(IF(AND(A1<0,B1<0 ),B1,A1))) The idea was that if there's a value in columns, A,B, and C, then the 'total' would read from column C (the most up-to-date), but if no value in C, then it would read from one of the other two based on which had the more current data. However, I noticed that sometimes my data skips a stage, so I may not have info in columns A or B; resulting in no value for the total. I've tried the above equation with "OR" instead of "AND" as well, but to no avail. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This should be easy...
How about =INDEX(A1:C1,1,MAX(IF(ISBLANK(A1:C1),0,COLUMN(A1:C 1))))
as an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "Jerry" wrote in message oups.com... But for some reason I can't get it to work. Here's the spreadsheet setup: I've got three columns of data that each describe a single transaction during separate stages. I've got another column that I'd like to use for determining the most up-to-date stage total. So, I came up with the following formula: IF((AND(A1<0,B1<0,C1<0)),C1,(IF(AND(A1<0,B1<0 ),B1,A1))) The idea was that if there's a value in columns, A,B, and C, then the 'total' would read from column C (the most up-to-date), but if no value in C, then it would read from one of the other two based on which had the more current data. However, I noticed that sometimes my data skips a stage, so I may not have info in columns A or B; resulting in no value for the total. I've tried the above equation with "OR" instead of "AND" as well, but to no avail. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This should be easy...
Thanks for your reply. I could not get your solution to work for me
though. Here's a little more info on what I'm looking to do: First, the equation I'm using with very limited success: =IF((AND(C3<0,D3<0,E3<0)),E3,(IF(AND(C3<0,D3< 0),D3,C3))) The spreadsheet setup: A B C D E 1 Est1 Est2 Act Total 2 Alpha 5.00 10.00 12.00 12.00 3 Beta 5.00 11.00 5.00 4 Gamma 10.00 11.00 - 5 Delta 10.00 - As you can see, the above equation only yields the correct Total in the Alpha row because all the fields have a value. In my actual spreadsheet there are 3 additional columns between the "Act" and "Rptd" that should not be counted in my equation, but if you use an array formula, would proabably have to be included. I'm not really familiar with arrays, so if you have any advice, I'd really appreciate it. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This should be easy...
Jerry,
Here is a variation that caters for the - =INDEX(B2:D2,1,MAX(IF(ISBLANK(B2:D2),0,IF(B2:D2="-",0,COLUMN(B2:D2)-1)))) I am not sure what you mean by the extra columns. -- HTH RP (remove nothere from the email address if mailing direct) "Jerry" wrote in message oups.com... Thanks for your reply. I could not get your solution to work for me though. Here's a little more info on what I'm looking to do: First, the equation I'm using with very limited success: =IF((AND(C3<0,D3<0,E3<0)),E3,(IF(AND(C3<0,D3< 0),D3,C3))) The spreadsheet setup: A B C D E 1 Est1 Est2 Act Total 2 Alpha 5.00 10.00 12.00 12.00 3 Beta 5.00 11.00 5.00 4 Gamma 10.00 11.00 - 5 Delta 10.00 - As you can see, the above equation only yields the correct Total in the Alpha row because all the fields have a value. In my actual spreadsheet there are 3 additional columns between the "Act" and "Rptd" that should not be counted in my equation, but if you use an array formula, would proabably have to be included. I'm not really familiar with arrays, so if you have any advice, I'd really appreciate it. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This should be easy...
Why not use the "Large" function the following example checks for the "largest" value in a selection: =LARGE(B4 D4,1) Put a "colon" between B4 and D4 - no spaces. I get a "smiley face" if I use a colon. This would be for column B thru D in row 4. The "1" is the for the largest if you wanted the second largest use a 2. Excel has a good help info on this function -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.hightechtalks.com/m390 View this thread: http://www.hightechtalks.com/t2297300 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This should be easy...
Why not use the "Large" function the following example checks for the "largest" value in a selection: =LARGE(B4:D4,1) This would be for column B thru D in row 4. The "1" is the for the largest if you wanted the second largest use a 2. Excel has a good help info on this function -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.hightechtalks.com/m390 View this thread: http://www.hightechtalks.com/t2297300 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This should be easy...
Because he wants the last one in the series, not the biggest
-- HTH RP (remove nothere from the email address if mailing direct) "wjohnson" wrote in message ... Why not use the "Large" function the following example checks for the "largest" value in a selection: =LARGE(B4:D4,1) This would be for column B thru D in row 4. The "1" is the for the largest if you wanted the second largest use a 2. Excel has a good help info on this function -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.hightechtalks.com/m390 View this thread: http://www.hightechtalks.com/t2297300 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This should be easy...
Bob,
Thank you. That worked beautifully. I'm trying to understand the equation, but am having trouble deciphering it (I've never used most of these functions for anything before). Honestly, I'm not being intellectually or google-search/excel-help lazy when I say this, but I really don't understand what's going on in the parenthases once you use the MAX function. Here's my thought process: By using INDEX, you're defining, within a range, what row and column should be displayed in the cell. So, "Index(B2:D2,1" is say to choose the current row in the range B2:D2. That's straightforward. So, and this is where I get really cloudy, "Max(IF(Isblank(B2:D2),0" is saying if the range B2:D2 is blank, then column equals zero? Otherwise, "IF(B2:D2="-",0,COLUMN(B2:D2)-1)," is saying if the range is literally a hyphen???, then it's equal to zero, otherwise it equals the number of columns in the range minus one??? Also realizing that the latter info is included in picking out the MAX value. Besides really wanting to understand how this formula and its functions work, I wanted to alter it such that I can account for the extra columns I mentioned in the previous post. What I meant by extra columns is that there are 3 columns that further describe the data numerically, but I did not want included in them in the formula for the "Total" cell. Thanks in advance for any illumination you can offer. -Ed |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This should be easy...
"Ed" wrote in message
ups.com... Bob, Thank you. That worked beautifully. I'm trying to understand the equation, but am having trouble deciphering it (I've never used most of these functions for anything before). Honestly, I'm not being intellectually or google-search/excel-help lazy when I say this, but I really don't understand what's going on in the parenthases once you use the MAX function. Here's my thought process: By using INDEX, you're defining, within a range, what row and column should be displayed in the cell. So, "Index(B2:D2,1" is say to choose the current row in the range B2:D2. That's straightforward. INDEX pulss back the value from a range for a specific row and column. As you say, the row number is just 1 as there is only 1,l but we have to calculate the column. So, and this is where I get really cloudy, "Max(IF(Isblank(B2:D2),0" is saying if the range B2:D2 is blank, then column equals zero? Otherwise, "IF(B2:D2="-",0,COLUMN(B2:D2)-1)," is saying if the range is literally a hyphen???, then it's equal to zero, otherwise it equals the number of columns in the range minus one??? Also realizing that the latter info is included in picking out the MAX value. To find the latest, I am tryning to work out the last (validly) inhabited. To find that, I test fro a hyphen, if there is one, I use that column number, else I use 0 (0 will never be MAX). It is an array formula, so by specifying a range, each cell in that range will be separately evaluated, and the results will be returned as an array which MAX works on. So the IF(B2:D2="-" tests B2, then C2, then D2 for a hyphen, and if not a hyphen, it adds the columne number of B2 *or C2, or D2) to the array, else it adds 0 to the array. Taking your example Gamma 10.00 11.00 - the formula IF(B2:D2="-",0,COLUMN(B2:D2)-1) would effectively evaluate as IF({10.00,11.00,"-"="-",0,{1,2,3})) which would return an array {1,2,0} which MAX evaluates to 2, which when passed to INDEX(B2:D2,... picks up the second column, i.e. C2 Besides really wanting to understand how this formula and its functions work, I wanted to alter it such that I can account for the extra columns I mentioned in the previous post. What I meant by extra columns is that there are 3 columns that further describe the data numerically, but I did not want included in them in the formula for the "Total" cell. Just extend the two ranges |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This should be easy...
Thanks again. I think I'm understanding the logic now.
The only other problem I'm having though is the "extra columns." Is there a way to exclude them from the range? There are times when they might have values that would result in an erroneous Total since expanding the range of your equation would include them in the Total. The purpose of these columns is more for an internal "check," so they are necessary. -Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Easy countif formula | Excel Discussion (Misc queries) | |||
OsCommerce - Easy Populate Script - CSV/TXT Conversion Problem. | Excel Discussion (Misc queries) | |||
new user with easy question? not easy for me | New Users to Excel | |||
Converting worksheets to workbooks. Is there an easy way? | Excel Discussion (Misc queries) | |||
Easy way to sum multiple worksheets | Excel Worksheet Functions |