|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 18th, 2010, 10:02 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which function will shorten IF-THEN-ELSE ?
Here's a simplified example of what I want to do:
Col A Col B Row 1 yes 5 Row 2 10 Row 3 yes 15 Row 4 yes 20 What is the formula that will automatically find all numbers in Col B that have a corresponding "yes" in Col A, then SUM them and multiply by 30% ? If done manually, the final result would then be the equivalent of =(5+15+20)*.30, which is 12. Thanks, 
|
|
#2
May 18th, 2010, 10:21 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Which function will shorten IF-THEN-ELSE ?
On May 18, 2:02*am, Jack Ryan wrote:
Here's a simplified example of what I want to do: * * * * * * *Col A * * *Col B Row 1 * * yes * * * * * 5 Row 2 * * * * * * * * * *10 Row 3 * * yes * * * * 15 Row 4 * * yes * * * * 20 What is the formula that will automatically find all numbers in Col B that have a corresponding "yes" in Col A, then SUM them and multiply by 30% ? *If done manually, the final result would then be the equivalent of =(5+15+20)*.30, which is 12. Thanks, Is this formula the best correct answer? =SUMIF(A1:A4,"yes",B1:B4)*0.3 Could a LOOKUP or OFFSET function be used to achieve the same answer? Thanks,
|
|
#3
May 18th, 2010, 10:51 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Which function will shorten IF-THEN-ELSE ?
Hi,
Use this =SUMPRODUCT((A1:A10="Yes")*(B1:B10))*0.3 -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Jack Ryan" wrote: Here's a simplified example of what I want to do: Col A Col B Row 1 yes 5 Row 2 10 Row 3 yes 15 Row 4 yes 20 What is the formula that will automatically find all numbers in Col B that have a corresponding "yes" in Col A, then SUM them and multiply by 30% ? If done manually, the final result would then be the equivalent of =(5+15+20)*.30, which is 12. Thanks, .
|
|
#4
May 18th, 2010, 10:58 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Which function will shorten IF-THEN-ELSE ?
Hi Jack
An alternative would be =SUMPRODUCT((A1:A4="yes")*B1:B4)*0.3 Your formula is fine, and SUMIF is far more efficient than Sumproduct, so on a large amount of data I would expect it to be considerably faster. -- Regards Roger Govier Jack R wrote: On May 18, 2:02 am, Jack Ryan wrote: Here's a simplified example of what I want to do: Col A Col B Row 1 yes 5 Row 2 10 Row 3 yes 15 Row 4 yes 20 What is the formula that will automatically find all numbers in Col B that have a corresponding "yes" in Col A, then SUM them and multiply by 30% ? If done manually, the final result would then be the equivalent of =(5+15+20)*.30, which is 12. Thanks, Is this formula the best correct answer? =SUMIF(A1:A4,"yes",B1:B4)*0.3 Could a LOOKUP or OFFSET function be used to achieve the same answer? Thanks,
|
|
#5
May 18th, 2010, 04:33 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Which function will shorten IF-THEN-ELSE ?
On May 18, 2:58*am, Roger Govier
wrote: Hi Jack An alternative would be =SUMPRODUCT((A1:A4="yes")*B1:B4)*0.3 Your formula is fine, and SUMIF is far more efficient than Sumproduct, so on a large amount of data I would expect it to be considerably faster. -- Regards Roger Govier Jack R wrote: On May 18, 2:02 am, Jack Ryan wrote: Here's a simplified example of what I want to do: * * * * * * *Col A * * *Col B Row 1 * * yes * * * * * 5 Row 2 * * * * * * * * * *10 Row 3 * * yes * * * * 15 Row 4 * * yes * * * * 20 What is the formula that will automatically find all numbers in Col B that have a corresponding "yes" in Col A, then SUM them and multiply by 30% ? *If done manually, the final result would then be the equivalent of =(5+15+20)*.30, which is 12. Thanks, Is this formula the best correct answer? =SUMIF(A1:A4,"yes",B1:B4)*0.3 Could a LOOKUP or OFFSET function be used to achieve the same answer? Thanks, I was just wondering how LOOKUP and OFFSET could be helpful in a larger version of my example. What would my example look like for LOOKUP and OFFSET to be useful?
|
|
#6
May 19th, 2010, 09:03 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Which function will shorten IF-THEN-ELSE ?
Hi Jack
Offset is a volatile function, and on a large spreadsheet is to be avoided wherever possible. It creates a huge overload in processing and will slow the sheet down enormously. Sumif is one of the most efficient functions, and your proposed solution is the best - IMHO -- Regards Roger Govier Jack R wrote: On May 18, 2:58 am, Roger Govier wrote: Hi Jack An alternative would be =SUMPRODUCT((A1:A4="yes")*B1:B4)*0.3 Your formula is fine, and SUMIF is far more efficient than Sumproduct, so on a large amount of data I would expect it to be considerably faster. -- Regards Roger Govier Jack R wrote: On May 18, 2:02 am, Jack Ryan wrote: Here's a simplified example of what I want to do: Col A Col B Row 1 yes 5 Row 2 10 Row 3 yes 15 Row 4 yes 20 What is the formula that will automatically find all numbers in Col B that have a corresponding "yes" in Col A, then SUM them and multiply by 30% ? If done manually, the final result would then be the equivalent of =(5+15+20)*.30, which is 12. Thanks, Is this formula the best correct answer? =SUMIF(A1:A4,"yes",B1:B4)*0.3 Could a LOOKUP or OFFSET function be used to achieve the same answer? Thanks, I was just wondering how LOOKUP and OFFSET could be helpful in a larger version of my example. What would my example look like for LOOKUP and OFFSET to be useful?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
