| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ignore calculation if text
We have a VLOOKUP fetching prices for various options.
On one option the user has to ask for a specific price. This means that in our price list we have the text "ASK" against one option. When the VLOOKUP retrieves a price, we multiply by the exchange rate. Excel doesn't want to calculate ASK x 0.7. How can we calculate unless the cell contains ASK in which case it just returns "ASK"? =IF(D4<"",VLOOKUP(D4,data!A4:B33,2,FALSE))*I4 I4 is exchange rate |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignore calculation if text
Try something like
=IF(VLOOKUP(D4,data!A4:B33,2,FALSE)="ASK","ASK",IF (D4<"",VLOOKUP(D4,data!A4:B33,2,FALSE))*I4) "AndyB" wrote in message ... We have a VLOOKUP fetching prices for various options. On one option the user has to ask for a specific price. This means that in our price list we have the text "ASK" against one option. When the VLOOKUP retrieves a price, we multiply by the exchange rate. Excel doesn't want to calculate ASK x 0.7. How can we calculate unless the cell contains ASK in which case it just returns "ASK"? =IF(D4<"",VLOOKUP(D4,data!A4:B33,2,FALSE))*I4 I4 is exchange rate |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignore calculation if text
It becomes a bit messy, but you could do this:
=IF(D4="","",IF(VLOOKUP(D4,data! A4:B33,2,0)="ASK","ASK",VLOOKUP(D4,data!A4:B33,2,0 )*I4)) Hope this helps. Pete On Nov 29, 11:25 am, AndyB wrote: We have a VLOOKUP fetching prices for various options. On one option the user has to ask for a specific price. This means that in our price list we have the text "ASK" against one option. When the VLOOKUP retrieves a price, we multiply by the exchange rate. Excel doesn't want to calculate ASK x 0.7. How can we calculate unless the cell contains ASK in which case it just returns "ASK"? =IF(D4<"",VLOOKUP(D4,data!A4:B33,2,FALSE))*I4 I4 is exchange rate |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignore calculation if text
Thanks,
No offense, but we did it Stephen's was as he posted 1st. Regards, Andy "Pete_UK" wrote: It becomes a bit messy, but you could do this: =IF(D4="","",IF(VLOOKUP(D4,data! A4:B33,2,0)="ASK","ASK",VLOOKUP(D4,data!A4:B33,2,0 )*I4)) Hope this helps. Pete On Nov 29, 11:25 am, AndyB wrote: We have a VLOOKUP fetching prices for various options. On one option the user has to ask for a specific price. This means that in our price list we have the text "ASK" against one option. When the VLOOKUP retrieves a price, we multiply by the exchange rate. Excel doesn't want to calculate ASK x 0.7. How can we calculate unless the cell contains ASK in which case it just returns "ASK"? =IF(D4<"",VLOOKUP(D4,data!A4:B33,2,FALSE))*I4 I4 is exchange rate |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignore calculation if text
That's okay, but you might like to check out what happens if D4 is
blank. Pete On Nov 29, 12:16 pm, AndyB wrote: Thanks, No offense, but we did it Stephen's was as he posted 1st. Regards, Andy "Pete_UK" wrote: It becomes a bit messy, but you could do this: =IF(D4="","",IF(VLOOKUP(D4,data! A4:B33,2,0)="ASK","ASK",VLOOKUP(D4,data!A4:B33,2,0 )*I4)) Hope this helps. Pete On Nov 29, 11:25 am, AndyB wrote: We have a VLOOKUP fetching prices for various options. On one option the user has to ask for a specific price. This means that in our price list we have the text "ASK" against one option. When the VLOOKUP retrieves a price, we multiply by the exchange rate. Excel doesn't want to calculate ASK x 0.7. How can we calculate unless the cell contains ASK in which case it just returns "ASK"? =IF(D4<"",VLOOKUP(D4,data!A4:B33,2,FALSE))*I4 I4 is exchange rate- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Median calculation and ignore zeros | Excel Discussion (Misc queries) | |||
| calculation to ignore a 0 (zero) value | Excel Worksheet Functions | |||
| use "button" to make calculation ignore a cell | Excel Worksheet Functions | |||
| Ignore errors when calculation average of multiple ranges | Excel Worksheet Functions | |||
| Can a calculation ignore text if it occurs in formula's cell range | Excel Worksheet Functions | |||
Linear Mode
