| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Percentages
thank you!! Exactly the answer I was looking for.
"sebastienm" wrote: The function has basically an IF and a VLOOKUP. VLOOKUP ----------- VLOOKUP( Search_What, Search_Where, Returned_Column, Approximate_Match) - Search_what = value to search for - Search_Where = Source Range including column where to search Search_What and column of values to return. Search_What is search only in first column of Search_Where. - Returned_Column = column number within Search_Where of the value to return - Aprrox_Matc = False if only searching for an exact match of search_what in first column of Search_Where. Else True (for approximate match). Eg: VLOOKUP(Z1, $A$10:$D$100, 3, False) - Search for value in Z1 - Search within first column of A10:D100, ie in A10:A100 - 3: When a match is found, return the corresponding value in the 3rd column of A10:D100, ie in C10,C100 - Search for an exact match (for more info on the lookup functions, check at the online file) FULL FUNCTION ------------------ =IF(UPPER(RIGHT(A2,6))=" TOTAL","",B2/VLOOKUP(A2&" TOTAL",$A:$B,2,FALSE)) means : If the 6 last characters of A2 in uppercase are " TOTAL" Then it is a total row so just return an empty string "" Else A2 is a regular item so: - search for its total (VLOOKUP): Search for value <A2 & " TOTAL" in column A and return corresponding value from B (ie total amount) - return < B2 / total_amount from the above line End If I hope this helps, -- Regards, Sébastien |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
| why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
| Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
| setting a range variable equal to the value of a string variable | Excel Programming | |||
| Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Threaded Mode
