Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formula Solution using Search and IF
I am using Office 2003 on Windows XP.
I need a spreadsheet formula I can write into the sheet from code that would use the following logic, but I'm stumped. I would like to do this using a single formula because it is really for another user and their sheet structure demands it: IF G6 LIKE "*FUTURE*" Then E6 * N6 * 10000 IF G6 LIKE "*SWAP*" Then E6 * N6 * 2500 IF G6 NOT LIKE "*FUTURE*" NOR LIKE "*SWAP*" Then blank (i.e. "") I know in place of "LIKE" I need to use "Search", but I can't crack the logic. Could someone please post a solution? Thanks much in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formula Solution using Search and IF
=IF(ISNUMBER(SEARCH(G6,"FUTURE")),E6*N6*10000,IF(I SNUMBER(SEARCH(G6,"SWAP"))
,E6*N6*2500,"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "XP" wrote in message ... I am using Office 2003 on Windows XP. I need a spreadsheet formula I can write into the sheet from code that would use the following logic, but I'm stumped. I would like to do this using a single formula because it is really for another user and their sheet structure demands it: IF G6 LIKE "*FUTURE*" Then E6 * N6 * 10000 IF G6 LIKE "*SWAP*" Then E6 * N6 * 2500 IF G6 NOT LIKE "*FUTURE*" NOR LIKE "*SWAP*" Then blank (i.e. "") I know in place of "LIKE" I need to use "Search", but I can't crack the logic. Could someone please post a solution? Thanks much in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formula Solution using Search and IF
Thanks Bob, but I need wild card searching for both FUTURE and SWAP. For
example, the cell could contain: "EOM Swap" or "Swing Swap", etc. Your formula works for "Swap" but not if the cell contains other characters... Can you please help be a bit further? "Bob Phillips" wrote: =IF(ISNUMBER(SEARCH(G6,"FUTURE")),E6*N6*10000,IF(I SNUMBER(SEARCH(G6,"SWAP")) ,E6*N6*2500,"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "XP" wrote in message ... I am using Office 2003 on Windows XP. I need a spreadsheet formula I can write into the sheet from code that would use the following logic, but I'm stumped. I would like to do this using a single formula because it is really for another user and their sheet structure demands it: IF G6 LIKE "*FUTURE*" Then E6 * N6 * 10000 IF G6 LIKE "*SWAP*" Then E6 * N6 * 2500 IF G6 NOT LIKE "*FUTURE*" NOR LIKE "*SWAP*" Then blank (i.e. "") I know in place of "LIKE" I need to use "Search", but I can't crack the logic. Could someone please post a solution? Thanks much in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formula Solution using Search and IF
Sorry for my previous post, I should not have doubted you, I had a typo. It
works great, thanks again!!! "Bob Phillips" wrote: =IF(ISNUMBER(SEARCH(G6,"FUTURE")),E6*N6*10000,IF(I SNUMBER(SEARCH(G6,"SWAP")) ,E6*N6*2500,"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "XP" wrote in message ... I am using Office 2003 on Windows XP. I need a spreadsheet formula I can write into the sheet from code that would use the following logic, but I'm stumped. I would like to do this using a single formula because it is really for another user and their sheet structure demands it: IF G6 LIKE "*FUTURE*" Then E6 * N6 * 10000 IF G6 LIKE "*SWAP*" Then E6 * N6 * 2500 IF G6 NOT LIKE "*FUTURE*" NOR LIKE "*SWAP*" Then blank (i.e. "") I know in place of "LIKE" I need to use "Search", but I can't crack the logic. Could someone please post a solution? Thanks much in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formula Solution using Search and IF
Just a comment, interesting use of "IsNumber" rather than "IsErr" which is
what I was trying to use. "IsNumber" simplifies it a lot, I like it. Thanks!!! "XP" wrote: Sorry for my previous post, I should not have doubted you, I had a typo. It works great, thanks again!!! "Bob Phillips" wrote: =IF(ISNUMBER(SEARCH(G6,"FUTURE")),E6*N6*10000,IF(I SNUMBER(SEARCH(G6,"SWAP")) ,E6*N6*2500,"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "XP" wrote in message ... I am using Office 2003 on Windows XP. I need a spreadsheet formula I can write into the sheet from code that would use the following logic, but I'm stumped. I would like to do this using a single formula because it is really for another user and their sheet structure demands it: IF G6 LIKE "*FUTURE*" Then E6 * N6 * 10000 IF G6 LIKE "*SWAP*" Then E6 * N6 * 2500 IF G6 NOT LIKE "*FUTURE*" NOR LIKE "*SWAP*" Then blank (i.e. "") I know in place of "LIKE" I need to use "Search", but I can't crack the logic. Could someone please post a solution? Thanks much in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formula Solution using Search and IF
You would have to use NOT(ISERR as you want to find a non-error.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "XP" wrote in message ... Just a comment, interesting use of "IsNumber" rather than "IsErr" which is what I was trying to use. "IsNumber" simplifies it a lot, I like it. Thanks!!! "XP" wrote: Sorry for my previous post, I should not have doubted you, I had a typo. It works great, thanks again!!! "Bob Phillips" wrote: =IF(ISNUMBER(SEARCH(G6,"FUTURE")),E6*N6*10000,IF(I SNUMBER(SEARCH(G6,"SWAP")) ,E6*N6*2500,"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "XP" wrote in message ... I am using Office 2003 on Windows XP. I need a spreadsheet formula I can write into the sheet from code that would use the following logic, but I'm stumped. I would like to do this using a single formula because it is really for another user and their sheet structure demands it: IF G6 LIKE "*FUTURE*" Then E6 * N6 * 10000 IF G6 LIKE "*SWAP*" Then E6 * N6 * 2500 IF G6 NOT LIKE "*FUTURE*" NOR LIKE "*SWAP*" Then blank (i.e. "") I know in place of "LIKE" I need to use "Search", but I can't crack the logic. Could someone please post a solution? Thanks much in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple problem complex solution? | Excel Worksheet Functions | |||
A rather difficult & complex statistical search formula needed | Excel Worksheet Functions | |||
Trying to find out a solution for a complex formula | Excel Discussion (Misc queries) | |||
Help wih complex formula or macro or solution | Excel Programming | |||
Help with complex formula or macro or solution | Excel Programming |