Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Translate in English please...
I have this excel form that gathers data from an external source and
references other columns in other sheets. I do not understand what this is trying to accomplish =IF(ROWS($1:1)<=COUNTIF(J$2:J$645,'Budget Input'!B$5),INDEX(K$2:K$645,SMALL(IF(J$2:J$645='Bu dget Input'!B$5,ROW(K$2:K$645)-ROW(K$2)+1),ROWS($1:1))),"") I think it says for a row (with $1 being variable) Count the column J and index them against column K, if a value in J equals Budget Input worksheet cell B5, then match the existing rows in K - ...here is where I am lost... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Translate in English please...
?B?c3NjaWFycmlubw==?=
wrote in : I have this excel form that gathers data from an external source and references other columns in other sheets. I do not understand what this is trying to accomplish =IF(ROWS($1:1)<=COUNTIF(J$2:J$645,'Budget Input'!B$5),INDEX(K$2:K$645,SMALL(IF(J$2:J$645='Bu dget Input'!B$5,ROW(K$2:K$645)-ROW(K$2)+1),ROWS($1:1))),"") I think it says for a row (with $1 being variable) Count the column J and index them against column K, if a value in J equals Budget Input worksheet cell B5, then match the existing rows in K - ...here is where I am lost... Looks like it's returning all the items in K2:K645 such that the corresponding value in J2:J645 is equal to the value in 'Budget Input'!B$5. You'd enter it as a single-cell array formula and copy it down. ROWS($1:1) gives a count of the rows between your starting row and the current row, whatever it is. COUNTIF() counts the items in the J range equal to the value in B5. So the outer IF() says, if the number of rows is less than or equal to the number of items in J2:J645 that match the Budget Input!B5 value, then return the INDEX() function, else return "". The INDEX returns an element from the range K2:K645. Which element? The element whose row is computed by the SMALL() function. SMALL() takes an array and a rank number k, and returns the k-th smallest element of the array. 'k' here can be seen to be the row number: ROWS($1:1) In this case, the array is created by the inner IF() formula. This formula must be entered as a single-cell array formula so the IF() will create an array of results. The inner IF() returns 2 options: 1) the element number of an item from K2:K645, the number is computed by the expression ROW(K$2:K$645)-ROW(K$2)+1 this returned when the value in column J = the value in B5 or 2) the value FALSE Since FALSE sorts after any number, the SMALL() function gives the item number of the k-th item in the K2:K645 range such that the value in the J2:J645 range matches the value in B5. Then the INDEX() function (remember it?) returns the value from the K2:K645 range with that item number. -- Dave Empey Remember, if you're doing any major experiments in stellar dynamics, always mount a scratch star first! --Richard Todd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How To Translate English to Arabic | Excel Discussion (Misc queries) | |||
Translate any english number into English word | Excel Discussion (Misc queries) | |||
How can I translate chinese text to english | Excel Discussion (Misc queries) | |||
Translate formula to english | Excel Discussion (Misc queries) | |||
translate function into english, anyone? | Excel Worksheet Functions |