Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Problem
Worksheet "Customer": In the cells in range U19:U30 users
post values, which are acted upon by formulas in an adjacent range X19:X30, i.e., formula in X19 acts on U19 value. Typical U range formulas: =IF(U19="","",IF(U19<0,(U19-V10)/(P10-V10),0)) =IF(U20="","",IF(U20<0,(U20-V10)/(P10-V10),0)) Etc. In cell W31 the following array formula looks at range X19:30 creating the last value in from the X column: {=INDIRECT(ADDRESS(MAX((ROW(19:30)* (X19:X30<""))),COLUMN(X:X)))} In cell X31 the following formula shows the value created by the array formula: =IF(ISERROR(W31)=TRUE,"",W31) The result: U cell value X31 shows 55% 55% 0% 0% nothing blank This is the desired outcome to this point. Now the twist - I need to put link formulas in the U19:U30 cells, to bring in cell values from worksheet "Rollup"; however, the Rollup cell value is #Div/0! in some cases, which makes the array formula in the Customer worksheet return a blank (nothing?) - instead of a legitimate number, say 55% if it appeared in the U column. Needed is: if the last cell value in the U column is, as in the above example, 55%, 0%, or blank, for the array formula to ignore the error values, and operate normally. I cannot figure out how to do this - Can someone help? Thanks, Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with array ref (I think!) | Excel Worksheet Functions | |||
Array problem, I think.. | Excel Worksheet Functions | |||
Array problem - TIA | Excel Worksheet Functions | |||
Array Problem | Excel Discussion (Misc queries) | |||
Array Problem, Help Please. | Excel Programming |