Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statements two
Awesome, both of those formulas worked great. Thanks! I have one followup
question. I have the following data: Category Date Amount A 11/1/01 100.00 A 1/1/00 100.00 B 12/31/04 200.00 C 1/1/00 300.00 As stated before, I have the formula to sum the values in the fields and divide the ones with invoice date before 1/1/01 by .03 and the ones after 1/1/01 by .029. However, is there a way to incorproate another formula that will only calculate the ones in category A, then in a sepearte cell calculate the ones in category B and so on and so fort. Thanks, Jason |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statements two
The formula I'm using to do the first set of calculations is:
=SUMPRODUCT(--(B2:B5=DATEVALUE("1/1/2001")),C2:C5)/0.029+SUMPRODUCT(--(B2:B5<DATEVALUE("1/1/2001")),C2:C5)/0.03 "Jason" wrote: Awesome, both of those formulas worked great. Thanks! I have one followup question. I have the following data: Category Date Amount A 11/1/01 100.00 A 1/1/00 100.00 B 12/31/04 200.00 C 1/1/00 300.00 As stated before, I have the formula to sum the values in the fields and divide the ones with invoice date before 1/1/01 by .03 and the ones after 1/1/01 by .029. However, is there a way to incorproate another formula that will only calculate the ones in category A, then in a sepearte cell calculate the ones in category B and so on and so fort. Thanks, Jason |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statements two
=SUM(IF(A1:A29="A",IF(B1:B29=--"2001-01-01",C1:C29/0.029,C1:C29/0.03)))
=SUM(IF(A1:A29="B",IF(B1:B29=--"2001-01-01",C1:C29/0.029,C1:C29/0.03))) etc. which are array formulae, they should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jason" wrote in message ... Awesome, both of those formulas worked great. Thanks! I have one followup question. I have the following data: Category Date Amount A 11/1/01 100.00 A 1/1/00 100.00 B 12/31/04 200.00 C 1/1/00 300.00 As stated before, I have the formula to sum the values in the fields and divide the ones with invoice date before 1/1/01 by .03 and the ones after 1/1/01 by .029. However, is there a way to incorproate another formula that will only calculate the ones in category A, then in a sepearte cell calculate the ones in category B and so on and so fort. Thanks, Jason |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statements two
for A's only maybe like this
=SUMPRODUCT(--(B2:B5=DATEVALUE("1/1/2001")),C2:C5,a2:a5="A")/0.029+SUMPRODUCT(--(B2:B5<DATEVALUE("1/1/2001")),C2:C5,a2:a5="A")/0.03 -- regards "Jason" wrote: The formula I'm using to do the first set of calculations is: =SUMPRODUCT(--(B2:B5=DATEVALUE("1/1/2001")),C2:C5)/0.029+SUMPRODUCT(--(B2:B5<DATEVALUE("1/1/2001")),C2:C5)/0.03 "Jason" wrote: Awesome, both of those formulas worked great. Thanks! I have one followup question. I have the following data: Category Date Amount A 11/1/01 100.00 A 1/1/00 100.00 B 12/31/04 200.00 C 1/1/00 300.00 As stated before, I have the formula to sum the values in the fields and divide the ones with invoice date before 1/1/01 by .03 and the ones after 1/1/01 by .029. However, is there a way to incorproate another formula that will only calculate the ones in category A, then in a sepearte cell calculate the ones in category B and so on and so fort. Thanks, Jason |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with IF statements | Excel Worksheet Functions | |||
IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
if and or statements | Excel Discussion (Misc queries) | |||
IF statements | Excel Discussion (Misc queries) | |||
If Statements... | Excel Discussion (Misc queries) |