| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Isolating letters for a formula
Hi guys,
I am currently applying the following formula to a speadsheet: =IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F DEF"),--(K6:K330)),"-") In a seperate formula I have also replaced "F DEF" with "C DEF" to return a different value. What I would now like to do is create a similar formula that incorparates both sets of data into one formula by identifying only the last three letters, i.e "DEF". Any ideas would be greatly appreciated. Thanks in advance, Ant |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Isolating letters for a formula
Use the RIGHT forumula. =RIGHT(A1,3) if you're referring to A1.
"Anto111" wrote: Hi guys, I am currently applying the following formula to a speadsheet: =IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F DEF"),--(K6:K330)),"-") In a seperate formula I have also replaced "F DEF" with "C DEF" to return a different value. What I would now like to do is create a similar formula that incorparates both sets of data into one formula by identifying only the last three letters, i.e "DEF". Any ideas would be greatly appreciated. Thanks in advance, Ant |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Isolating letters for a formula
Hi,
thanks for that, just been reading up on that by coincidence but not sure how to incorparate it into the formula? Cheers for your time, Ant "Anto111" wrote: Hi guys, I am currently applying the following formula to a speadsheet: =IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F DEF"),--(K6:K330)),"-") In a seperate formula I have also replaced "F DEF" with "C DEF" to return a different value. What I would now like to do is create a similar formula that incorparates both sets of data into one formula by identifying only the last three letters, i.e "DEF". Any ideas would be greatly appreciated. Thanks in advance, Ant |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Isolating letters for a formula
Try this:
=IFERROR(SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),K6:K33)/SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),--(K6:K330)),"-") Or, maybe this: =IFERROR(SUMIF(B6:B33,"*DEF",K6:K33)/COUNTIFS(B6:B33,"*DEF",K6:K33,"0"),"-") -- Biff Microsoft Excel MVP "Anto111" wrote in message ... Hi guys, I am currently applying the following formula to a speadsheet: =IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F DEF"),--(K6:K330)),"-") In a seperate formula I have also replaced "F DEF" with "C DEF" to return a different value. What I would now like to do is create a similar formula that incorparates both sets of data into one formula by identifying only the last three letters, i.e "DEF". Any ideas would be greatly appreciated. Thanks in advance, Ant |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Isolating letters for a formula
You could either nest your original formula in RIGHT like this:
=RIGHT(IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F DEF"),--(K6:K330)),"-"),3) or use RIGHT to refer to what you have now from another cell. So if your =IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F DEF"),--(K6:K330)),"-") formula is in A1, use =RIGHT(A1,3) where you want it to go. "Anto111" wrote: Hi, thanks for that, just been reading up on that by coincidence but not sure how to incorparate it into the formula? Cheers for your time, Ant "Anto111" wrote: Hi guys, I am currently applying the following formula to a speadsheet: =IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F DEF"),--(K6:K330)),"-") In a seperate formula I have also replaced "F DEF" with "C DEF" to return a different value. What I would now like to do is create a similar formula that incorparates both sets of data into one formula by identifying only the last three letters, i.e "DEF". Any ideas would be greatly appreciated. Thanks in advance, Ant |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Isolating letters for a formula
T. Valko, your an absolute magician,
thank you. "T. Valko" wrote: Try this: =IFERROR(SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),K6:K33)/SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),--(K6:K330)),"-") Or, maybe this: =IFERROR(SUMIF(B6:B33,"*DEF",K6:K33)/COUNTIFS(B6:B33,"*DEF",K6:K33,"0"),"-") -- Biff Microsoft Excel MVP "Anto111" wrote in message ... Hi guys, I am currently applying the following formula to a speadsheet: =IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F DEF"),--(K6:K330)),"-") In a seperate formula I have also replaced "F DEF" with "C DEF" to return a different value. What I would now like to do is create a similar formula that incorparates both sets of data into one formula by identifying only the last three letters, i.e "DEF". Any ideas would be greatly appreciated. Thanks in advance, Ant |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Isolating letters for a formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Anto111" wrote in message ... T. Valko, your an absolute magician, thank you. "T. Valko" wrote: Try this: =IFERROR(SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),K6:K33)/SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),--(K6:K330)),"-") Or, maybe this: =IFERROR(SUMIF(B6:B33,"*DEF",K6:K33)/COUNTIFS(B6:B33,"*DEF",K6:K33,"0"),"-") -- Biff Microsoft Excel MVP "Anto111" wrote in message ... Hi guys, I am currently applying the following formula to a speadsheet: =IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F DEF"),--(K6:K330)),"-") In a seperate formula I have also replaced "F DEF" with "C DEF" to return a different value. What I would now like to do is create a similar formula that incorparates both sets of data into one formula by identifying only the last three letters, i.e "DEF". Any ideas would be greatly appreciated. Thanks in advance, Ant |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Isolating letters for a formula
sb, they both worked. I really appreciate your time, thanks. ;-)
"sb1920alk" wrote: You could either nest your original formula in RIGHT like this: =RIGHT(IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F DEF"),--(K6:K330)),"-"),3) or use RIGHT to refer to what you have now from another cell. So if your =IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F DEF"),--(K6:K330)),"-") formula is in A1, use =RIGHT(A1,3) where you want it to go. "Anto111" wrote: Hi, thanks for that, just been reading up on that by coincidence but not sure how to incorparate it into the formula? Cheers for your time, Ant "Anto111" wrote: Hi guys, I am currently applying the following formula to a speadsheet: =IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F DEF"),--(K6:K330)),"-") In a seperate formula I have also replaced "F DEF" with "C DEF" to return a different value. What I would now like to do is create a similar formula that incorparates both sets of data into one formula by identifying only the last three letters, i.e "DEF". Any ideas would be greatly appreciated. Thanks in advance, Ant |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Isolating text and numbers | Excel Discussion (Misc queries) | |||
| Isolating mailadresses | Excel Worksheet Functions | |||
| Isolating months | Excel Discussion (Misc queries) | |||
| Isolating Email addresses | Excel Worksheet Functions | |||
| Isolating a Keyboard Shortcut | Setting up and Configuration of Excel | |||
Linear Mode
