| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested If returns error (too many arguments)
Here is my If statement. I am getting an error message "too many arguments".
Can you tell me what I'm missing? =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Thanks in advance, |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If returns error (too many arguments)
I tried to decipher what you are meaning, but I can't tell where one IF
statement ends, and the next begins.... =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Might I suggest writing the formula inside out? in other words, writing your last IF statement first, then modifying it with the 2nd to last. For example: =IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4,0) : Note, this assumes that the ,0 is for this IF statement. Your statement is difficult to decipher if the ,0 is for this one, or for the original statement. Go Back to the statement, add the next IF statement outside of your first (which I 'think' your last IF statement is if your 2nd to last is negative.... =IF(R4=S4,T4-U4,last if statement) etc. If this doesn't help, let us know what conditions you are testing for, and what the result you want to be is. -- John C "plally" wrote: Here is my If statement. I am getting an error message "too many arguments". Can you tell me what I'm missing? =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Thanks in advance, |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If returns error (too many arguments)
So, this is what I'm trying to do.
I'm trying to calculate premiums (PREM) for a given year (PREM YEAR) based on the policy issue date (EFF DATE) and policy termination date (TERM DATE). I also need to look at policy issue year (EFF YR) and policy termination year (TERM YR). All policies have an annual premium (ANN PREM). If the EFF YR PREM Year or TERM YR < PREM Year, Prem = 0 If EFF YR = PREM YEAR and TERM YR PREM Year, then perform the following Calculation: (((last day of prem yr - effective date)/365)*ANN PREM) If EFF YR = TERM YEAR, perform the following calculation: (((TERM DATE - EFF DATE)/365)/365*ANN PREM) If EFF YR < PREM Year and TERM YR = PREM Year, perform the following calculation: ((TERM DATE - day 1 of Prem Year)/365)*ANN PREM) my formula points to a cell that includes PREM YEAR, Day 1 of Prem Year and Last day of Prem Yr. Thanks for taking a look at this. "John C" wrote: I tried to decipher what you are meaning, but I can't tell where one IF statement ends, and the next begins.... =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Might I suggest writing the formula inside out? in other words, writing your last IF statement first, then modifying it with the 2nd to last. For example: =IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4,0) : Note, this assumes that the ,0 is for this IF statement. Your statement is difficult to decipher if the ,0 is for this one, or for the original statement. Go Back to the statement, add the next IF statement outside of your first (which I 'think' your last IF statement is if your 2nd to last is negative.... =IF(R4=S4,T4-U4,last if statement) etc. If this doesn't help, let us know what conditions you are testing for, and what the result you want to be is. -- John C "plally" wrote: Here is my If statement. I am getting an error message "too many arguments". Can you tell me what I'm missing? =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Thanks in advance, |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If returns error (too many arguments)
I modified your formula a bit to accomodate my cells. Here is the modified
version. When I inserted the six end parenthesis, Excel did not like that and suggested changes to the formula. =IF(OR(R4V3,S4<V3,),0, (IF(AND(R4=V3,S4V3),YEARFRAC(V2-B4)*U4), IF(R4=S4,T4*U4), IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4))) I am now getting the error: #N/A "Roger Govier" wrote: Hi Using some of the explanation for your second posting, and combining it with your first posting, I think that this is what you want. =if(or(R4V3,S4<V3,),0, if(and(R4=V3,S4V3),yearfrac(V2-B4)*U4), if(R4=S4,T4*U4), (((TERM DATE - EFF DATE)/365)/365*ANN PREM), if(and(R4<V3,S4=V3),((C4-V1)/365)*U4)))))) You will need to translate the TERM DATE etc to the relevant cells holding the values. -- Regards Roger Govier "plally" wrote in message ... So, this is what I'm trying to do. I'm trying to calculate premiums (PREM) for a given year (PREM YEAR) based on the policy issue date (EFF DATE) and policy termination date (TERM DATE). I also need to look at policy issue year (EFF YR) and policy termination year (TERM YR). All policies have an annual premium (ANN PREM). If the EFF YR PREM Year or TERM YR < PREM Year, Prem = 0 If EFF YR = PREM YEAR and TERM YR PREM Year, then perform the following Calculation: (((last day of prem yr - effective date)/365)*ANN PREM) If EFF YR = TERM YEAR, perform the following calculation: (((TERM DATE - EFF DATE)/365)/365*ANN PREM) If EFF YR < PREM Year and TERM YR = PREM Year, perform the following calculation: ((TERM DATE - day 1 of Prem Year)/365)*ANN PREM) my formula points to a cell that includes PREM YEAR, Day 1 of Prem Year and Last day of Prem Yr. Thanks for taking a look at this. "John C" wrote: I tried to decipher what you are meaning, but I can't tell where one IF statement ends, and the next begins.... =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Might I suggest writing the formula inside out? in other words, writing your last IF statement first, then modifying it with the 2nd to last. For example: =IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4,0) : Note, this assumes that the ,0 is for this IF statement. Your statement is difficult to decipher if the ,0 is for this one, or for the original statement. Go Back to the statement, add the next IF statement outside of your first (which I 'think' your last IF statement is if your 2nd to last is negative.... =IF(R4=S4,T4-U4,last if statement) etc. If this doesn't help, let us know what conditions you are testing for, and what the result you want to be is. -- John C "plally" wrote: Here is my If statement. I am getting an error message "too many arguments". Can you tell me what I'm missing? =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Thanks in advance, |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If returns error (too many arguments)
Okay, let's rework the formula. If I understand you correctly, you are
wanting to calculate the total premium for a single given year, based on the policy effective date and policy termination date. The Premium date is the year that you are trying to calculate the premium for. You are trying to calculate the number of days in the Premium Year, and then use the number of days of the premium year, divide by 365, then multiply by the Annual premium, and that is your resultant. That being said, why not use a formula like so: =(MAX(MIN(TermYear2,DATE(PremYear,12,31)),DATE(Pre mYear,1,1))-MIN(MAX(EffYear2,DATE(PremYear,1,1)),DATE(PremYear ,12,31)))/365*AnnPremium TermYear2 = actual end date of policy EffYear2 = actual start date of policy PremYear = year you are trying to calculate for. AnnPremium = Annual premium amount. It first determines the earliest date 12/31 of Prem Year, or Termination date (the MIN). Then it takes that and calculates which is later, that or 1/1 of Prem Year. Then it determines the later of 2 dates, the effective date of the policy, or 1/1 of Prem Year, and compares that to which is earliest, that or 12/31 of Prem Year. If TermDate is less than PremYear, both calculations will come up to 1/1 of prem year. If EffDate is greater than PremYear, both calculations will come up to 12/31 of prem year. It ill take the first value and subtract the second value, divide this by 365, and then multiply it by the Annual Premium. Hope this helps. -- John C "plally" wrote: I modified your formula a bit to accomodate my cells. Here is the modified version. When I inserted the six end parenthesis, Excel did not like that and suggested changes to the formula. =IF(OR(R4V3,S4<V3,),0, (IF(AND(R4=V3,S4V3),YEARFRAC(V2-B4)*U4), IF(R4=S4,T4*U4), IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4))) I am now getting the error: #N/A "Roger Govier" wrote: Hi Using some of the explanation for your second posting, and combining it with your first posting, I think that this is what you want. =if(or(R4V3,S4<V3,),0, if(and(R4=V3,S4V3),yearfrac(V2-B4)*U4), if(R4=S4,T4*U4), (((TERM DATE - EFF DATE)/365)/365*ANN PREM), if(and(R4<V3,S4=V3),((C4-V1)/365)*U4)))))) You will need to translate the TERM DATE etc to the relevant cells holding the values. -- Regards Roger Govier "plally" wrote in message ... So, this is what I'm trying to do. I'm trying to calculate premiums (PREM) for a given year (PREM YEAR) based on the policy issue date (EFF DATE) and policy termination date (TERM DATE). I also need to look at policy issue year (EFF YR) and policy termination year (TERM YR). All policies have an annual premium (ANN PREM). If the EFF YR PREM Year or TERM YR < PREM Year, Prem = 0 If EFF YR = PREM YEAR and TERM YR PREM Year, then perform the following Calculation: (((last day of prem yr - effective date)/365)*ANN PREM) If EFF YR = TERM YEAR, perform the following calculation: (((TERM DATE - EFF DATE)/365)/365*ANN PREM) If EFF YR < PREM Year and TERM YR = PREM Year, perform the following calculation: ((TERM DATE - day 1 of Prem Year)/365)*ANN PREM) my formula points to a cell that includes PREM YEAR, Day 1 of Prem Year and Last day of Prem Yr. Thanks for taking a look at this. "John C" wrote: I tried to decipher what you are meaning, but I can't tell where one IF statement ends, and the next begins.... =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Might I suggest writing the formula inside out? in other words, writing your last IF statement first, then modifying it with the 2nd to last. For example: =IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4,0) : Note, this assumes that the ,0 is for this IF statement. Your statement is difficult to decipher if the ,0 is for this one, or for the original statement. Go Back to the statement, add the next IF statement outside of your first (which I 'think' your last IF statement is if your 2nd to last is negative.... =IF(R4=S4,T4-U4,last if statement) etc. If this doesn't help, let us know what conditions you are testing for, and what the result you want to be is. -- John C "plally" wrote: Here is my If statement. I am getting an error message "too many arguments". Can you tell me what I'm missing? =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Thanks in advance, |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If returns error (too many arguments)
Nice Logic. Way above my every-day use of Excel!
Anyway, when I insert the formula I get an error relating to "negative dates or times" =MAX(MIN(C2,DATE(V1,12,31),DATE(V1,1,1))-(MIN(MAX(B2,DATE(V1,1,1)),DATE(V1,12,31))/365*U2)) C2=policy end date V1=Prem Year B2=Policy issue date Excel suggested the extra parenthesis'. "John C" wrote: Okay, let's rework the formula. If I understand you correctly, you are wanting to calculate the total premium for a single given year, based on the policy effective date and policy termination date. The Premium date is the year that you are trying to calculate the premium for. You are trying to calculate the number of days in the Premium Year, and then use the number of days of the premium year, divide by 365, then multiply by the Annual premium, and that is your resultant. That being said, why not use a formula like so: =(MAX(MIN(TermYear2,DATE(PremYear,12,31)),DATE(Pre mYear,1,1))-MIN(MAX(EffYear2,DATE(PremYear,1,1)),DATE(PremYear ,12,31)))/365*AnnPremium TermYear2 = actual end date of policy EffYear2 = actual start date of policy PremYear = year you are trying to calculate for. AnnPremium = Annual premium amount. It first determines the earliest date 12/31 of Prem Year, or Termination date (the MIN). Then it takes that and calculates which is later, that or 1/1 of Prem Year. Then it determines the later of 2 dates, the effective date of the policy, or 1/1 of Prem Year, and compares that to which is earliest, that or 12/31 of Prem Year. If TermDate is less than PremYear, both calculations will come up to 1/1 of prem year. If EffDate is greater than PremYear, both calculations will come up to 12/31 of prem year. It ill take the first value and subtract the second value, divide this by 365, and then multiply it by the Annual Premium. Hope this helps. -- John C "plally" wrote: I modified your formula a bit to accomodate my cells. Here is the modified version. When I inserted the six end parenthesis, Excel did not like that and suggested changes to the formula. =IF(OR(R4V3,S4<V3,),0, (IF(AND(R4=V3,S4V3),YEARFRAC(V2-B4)*U4), IF(R4=S4,T4*U4), IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4))) I am now getting the error: #N/A "Roger Govier" wrote: Hi Using some of the explanation for your second posting, and combining it with your first posting, I think that this is what you want. =if(or(R4V3,S4<V3,),0, if(and(R4=V3,S4V3),yearfrac(V2-B4)*U4), if(R4=S4,T4*U4), (((TERM DATE - EFF DATE)/365)/365*ANN PREM), if(and(R4<V3,S4=V3),((C4-V1)/365)*U4)))))) You will need to translate the TERM DATE etc to the relevant cells holding the values. -- Regards Roger Govier "plally" wrote in message ... So, this is what I'm trying to do. I'm trying to calculate premiums (PREM) for a given year (PREM YEAR) based on the policy issue date (EFF DATE) and policy termination date (TERM DATE). I also need to look at policy issue year (EFF YR) and policy termination year (TERM YR). All policies have an annual premium (ANN PREM). If the EFF YR PREM Year or TERM YR < PREM Year, Prem = 0 If EFF YR = PREM YEAR and TERM YR PREM Year, then perform the following Calculation: (((last day of prem yr - effective date)/365)*ANN PREM) If EFF YR = TERM YEAR, perform the following calculation: (((TERM DATE - EFF DATE)/365)/365*ANN PREM) If EFF YR < PREM Year and TERM YR = PREM Year, perform the following calculation: ((TERM DATE - day 1 of Prem Year)/365)*ANN PREM) my formula points to a cell that includes PREM YEAR, Day 1 of Prem Year and Last day of Prem Yr. Thanks for taking a look at this. "John C" wrote: I tried to decipher what you are meaning, but I can't tell where one IF statement ends, and the next begins.... =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Might I suggest writing the formula inside out? in other words, writing your last IF statement first, then modifying it with the 2nd to last. For example: =IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4,0) : Note, this assumes that the ,0 is for this IF statement. Your statement is difficult to decipher if the ,0 is for this one, or for the original statement. Go Back to the statement, add the next IF statement outside of your first (which I 'think' your last IF statement is if your 2nd to last is negative.... =IF(R4=S4,T4-U4,last if statement) etc. If this doesn't help, let us know what conditions you are testing for, and what the result you want to be is. -- John C "plally" wrote: Here is my If statement. I am getting an error message "too many arguments". Can you tell me what I'm missing? =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Thanks in advance, |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If returns error (too many arguments)
Hi
Yes, I got carried away with the parentheses at the end. Should have been =IF(OR(R4V3,S4<V3),0, IF(AND(R4=V3,S4V3),YEARFRAC(V2-B4)*U4, IF(R4=S4,T4*U4, IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4)))) Either you, or Excel put them in the wrong place!!! -- Regards Roger Govier "plally" wrote in message ... I modified your formula a bit to accomodate my cells. Here is the modified version. When I inserted the six end parenthesis, Excel did not like that and suggested changes to the formula. =IF(OR(R4V3,S4<V3,),0, (IF(AND(R4=V3,S4V3),YEARFRAC(V2-B4)*U4), IF(R4=S4,T4*U4), IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4))) I am now getting the error: #N/A "Roger Govier" wrote: Hi Using some of the explanation for your second posting, and combining it with your first posting, I think that this is what you want. =if(or(R4V3,S4<V3,),0, if(and(R4=V3,S4V3),yearfrac(V2-B4)*U4), if(R4=S4,T4*U4), (((TERM DATE - EFF DATE)/365)/365*ANN PREM), if(and(R4<V3,S4=V3),((C4-V1)/365)*U4)))))) You will need to translate the TERM DATE etc to the relevant cells holding the values. -- Regards Roger Govier "plally" wrote in message ... So, this is what I'm trying to do. I'm trying to calculate premiums (PREM) for a given year (PREM YEAR) based on the policy issue date (EFF DATE) and policy termination date (TERM DATE). I also need to look at policy issue year (EFF YR) and policy termination year (TERM YR). All policies have an annual premium (ANN PREM). If the EFF YR PREM Year or TERM YR < PREM Year, Prem = 0 If EFF YR = PREM YEAR and TERM YR PREM Year, then perform the following Calculation: (((last day of prem yr - effective date)/365)*ANN PREM) If EFF YR = TERM YEAR, perform the following calculation: (((TERM DATE - EFF DATE)/365)/365*ANN PREM) If EFF YR < PREM Year and TERM YR = PREM Year, perform the following calculation: ((TERM DATE - day 1 of Prem Year)/365)*ANN PREM) my formula points to a cell that includes PREM YEAR, Day 1 of Prem Year and Last day of Prem Yr. Thanks for taking a look at this. "John C" wrote: I tried to decipher what you are meaning, but I can't tell where one IF statement ends, and the next begins.... =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Might I suggest writing the formula inside out? in other words, writing your last IF statement first, then modifying it with the 2nd to last. For example: =IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4,0) : Note, this assumes that the ,0 is for this IF statement. Your statement is difficult to decipher if the ,0 is for this one, or for the original statement. Go Back to the statement, add the next IF statement outside of your first (which I 'think' your last IF statement is if your 2nd to last is negative.... =IF(R4=S4,T4-U4,last if statement) etc. If this doesn't help, let us know what conditions you are testing for, and what the result you want to be is. -- John C "plally" wrote: Here is my If statement. I am getting an error message "too many arguments". Can you tell me what I'm missing? =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Thanks in advance, |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If returns error (too many arguments)
You have re-arranged some of the parentheses. Copy and paste the following
formula: =(MAX(MIN(C2,DATE(V1,12,31)),DATE(V1,1,1))-MIN(MAX(B2,DATE(V1,1,1)),DATE(V1,12,31)))/365*AnnPremium Change AnnPremium to whatever cell reference you need to, but the /365 * AnnPremium do not need to be inside any parentheses. -- John C "plally" wrote: Nice Logic. Way above my every-day use of Excel! Anyway, when I insert the formula I get an error relating to "negative dates or times" =MAX(MIN(C2,DATE(V1,12,31),DATE(V1,1,1))-(MIN(MAX(B2,DATE(V1,1,1)),DATE(V1,12,31))/365*U2)) C2=policy end date V1=Prem Year B2=Policy issue date Excel suggested the extra parenthesis'. "John C" wrote: Okay, let's rework the formula. If I understand you correctly, you are wanting to calculate the total premium for a single given year, based on the policy effective date and policy termination date. The Premium date is the year that you are trying to calculate the premium for. You are trying to calculate the number of days in the Premium Year, and then use the number of days of the premium year, divide by 365, then multiply by the Annual premium, and that is your resultant. That being said, why not use a formula like so: =(MAX(MIN(TermYear2,DATE(PremYear,12,31)),DATE(Pre mYear,1,1))-MIN(MAX(EffYear2,DATE(PremYear,1,1)),DATE(PremYear ,12,31)))/365*AnnPremium TermYear2 = actual end date of policy EffYear2 = actual start date of policy PremYear = year you are trying to calculate for. AnnPremium = Annual premium amount. It first determines the earliest date 12/31 of Prem Year, or Termination date (the MIN). Then it takes that and calculates which is later, that or 1/1 of Prem Year. Then it determines the later of 2 dates, the effective date of the policy, or 1/1 of Prem Year, and compares that to which is earliest, that or 12/31 of Prem Year. If TermDate is less than PremYear, both calculations will come up to 1/1 of prem year. If EffDate is greater than PremYear, both calculations will come up to 12/31 of prem year. It ill take the first value and subtract the second value, divide this by 365, and then multiply it by the Annual Premium. Hope this helps. -- John C "plally" wrote: I modified your formula a bit to accomodate my cells. Here is the modified version. When I inserted the six end parenthesis, Excel did not like that and suggested changes to the formula. =IF(OR(R4V3,S4<V3,),0, (IF(AND(R4=V3,S4V3),YEARFRAC(V2-B4)*U4), IF(R4=S4,T4*U4), IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4))) I am now getting the error: #N/A "Roger Govier" wrote: Hi Using some of the explanation for your second posting, and combining it with your first posting, I think that this is what you want. =if(or(R4V3,S4<V3,),0, if(and(R4=V3,S4V3),yearfrac(V2-B4)*U4), if(R4=S4,T4*U4), (((TERM DATE - EFF DATE)/365)/365*ANN PREM), if(and(R4<V3,S4=V3),((C4-V1)/365)*U4)))))) You will need to translate the TERM DATE etc to the relevant cells holding the values. -- Regards Roger Govier "plally" wrote in message ... So, this is what I'm trying to do. I'm trying to calculate premiums (PREM) for a given year (PREM YEAR) based on the policy issue date (EFF DATE) and policy termination date (TERM DATE). I also need to look at policy issue year (EFF YR) and policy termination year (TERM YR). All policies have an annual premium (ANN PREM). If the EFF YR PREM Year or TERM YR < PREM Year, Prem = 0 If EFF YR = PREM YEAR and TERM YR PREM Year, then perform the following Calculation: (((last day of prem yr - effective date)/365)*ANN PREM) If EFF YR = TERM YEAR, perform the following calculation: (((TERM DATE - EFF DATE)/365)/365*ANN PREM) If EFF YR < PREM Year and TERM YR = PREM Year, perform the following calculation: ((TERM DATE - day 1 of Prem Year)/365)*ANN PREM) my formula points to a cell that includes PREM YEAR, Day 1 of Prem Year and Last day of Prem Yr. Thanks for taking a look at this. "John C" wrote: I tried to decipher what you are meaning, but I can't tell where one IF statement ends, and the next begins.... =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Might I suggest writing the formula inside out? in other words, writing your last IF statement first, then modifying it with the 2nd to last. For example: =IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4,0) : Note, this assumes that the ,0 is for this IF statement. Your statement is difficult to decipher if the ,0 is for this one, or for the original statement. Go Back to the statement, add the next IF statement outside of your first (which I 'think' your last IF statement is if your 2nd to last is negative.... =IF(R4=S4,T4-U4,last if statement) etc. If this doesn't help, let us know what conditions you are testing for, and what the result you want to be is. -- John C "plally" wrote: Here is my If statement. I am getting an error message "too many arguments". Can you tell me what I'm missing? =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Thanks in advance, |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If returns error (too many arguments)
That worked. Thanks so much.
Pauline L Lally "John C" wrote: You have re-arranged some of the parentheses. Copy and paste the following formula: =(MAX(MIN(C2,DATE(V1,12,31)),DATE(V1,1,1))-MIN(MAX(B2,DATE(V1,1,1)),DATE(V1,12,31)))/365*AnnPremium Change AnnPremium to whatever cell reference you need to, but the /365 * AnnPremium do not need to be inside any parentheses. -- John C "plally" wrote: Nice Logic. Way above my every-day use of Excel! Anyway, when I insert the formula I get an error relating to "negative dates or times" =MAX(MIN(C2,DATE(V1,12,31),DATE(V1,1,1))-(MIN(MAX(B2,DATE(V1,1,1)),DATE(V1,12,31))/365*U2)) C2=policy end date V1=Prem Year B2=Policy issue date Excel suggested the extra parenthesis'. "John C" wrote: Okay, let's rework the formula. If I understand you correctly, you are wanting to calculate the total premium for a single given year, based on the policy effective date and policy termination date. The Premium date is the year that you are trying to calculate the premium for. You are trying to calculate the number of days in the Premium Year, and then use the number of days of the premium year, divide by 365, then multiply by the Annual premium, and that is your resultant. That being said, why not use a formula like so: =(MAX(MIN(TermYear2,DATE(PremYear,12,31)),DATE(Pre mYear,1,1))-MIN(MAX(EffYear2,DATE(PremYear,1,1)),DATE(PremYear ,12,31)))/365*AnnPremium TermYear2 = actual end date of policy EffYear2 = actual start date of policy PremYear = year you are trying to calculate for. AnnPremium = Annual premium amount. It first determines the earliest date 12/31 of Prem Year, or Termination date (the MIN). Then it takes that and calculates which is later, that or 1/1 of Prem Year. Then it determines the later of 2 dates, the effective date of the policy, or 1/1 of Prem Year, and compares that to which is earliest, that or 12/31 of Prem Year. If TermDate is less than PremYear, both calculations will come up to 1/1 of prem year. If EffDate is greater than PremYear, both calculations will come up to 12/31 of prem year. It ill take the first value and subtract the second value, divide this by 365, and then multiply it by the Annual Premium. Hope this helps. -- John C "plally" wrote: I modified your formula a bit to accomodate my cells. Here is the modified version. When I inserted the six end parenthesis, Excel did not like that and suggested changes to the formula. =IF(OR(R4V3,S4<V3,),0, (IF(AND(R4=V3,S4V3),YEARFRAC(V2-B4)*U4), IF(R4=S4,T4*U4), IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4))) I am now getting the error: #N/A "Roger Govier" wrote: Hi Using some of the explanation for your second posting, and combining it with your first posting, I think that this is what you want. =if(or(R4V3,S4<V3,),0, if(and(R4=V3,S4V3),yearfrac(V2-B4)*U4), if(R4=S4,T4*U4), (((TERM DATE - EFF DATE)/365)/365*ANN PREM), if(and(R4<V3,S4=V3),((C4-V1)/365)*U4)))))) You will need to translate the TERM DATE etc to the relevant cells holding the values. -- Regards Roger Govier "plally" wrote in message ... So, this is what I'm trying to do. I'm trying to calculate premiums (PREM) for a given year (PREM YEAR) based on the policy issue date (EFF DATE) and policy termination date (TERM DATE). I also need to look at policy issue year (EFF YR) and policy termination year (TERM YR). All policies have an annual premium (ANN PREM). If the EFF YR PREM Year or TERM YR < PREM Year, Prem = 0 If EFF YR = PREM YEAR and TERM YR PREM Year, then perform the following Calculation: (((last day of prem yr - effective date)/365)*ANN PREM) If EFF YR = TERM YEAR, perform the following calculation: (((TERM DATE - EFF DATE)/365)/365*ANN PREM) If EFF YR < PREM Year and TERM YR = PREM Year, perform the following calculation: ((TERM DATE - day 1 of Prem Year)/365)*ANN PREM) my formula points to a cell that includes PREM YEAR, Day 1 of Prem Year and Last day of Prem Yr. Thanks for taking a look at this. "John C" wrote: I tried to decipher what you are meaning, but I can't tell where one IF statement ends, and the next begins.... =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Might I suggest writing the formula inside out? in other words, writing your last IF statement first, then modifying it with the 2nd to last. For example: =IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4,0) : Note, this assumes that the ,0 is for this IF statement. Your statement is difficult to decipher if the ,0 is for this one, or for the original statement. Go Back to the statement, add the next IF statement outside of your first (which I 'think' your last IF statement is if your 2nd to last is negative.... =IF(R4=S4,T4-U4,last if statement) etc. If this doesn't help, let us know what conditions you are testing for, and what the result you want to be is. -- John C "plally" wrote: Here is my If statement. I am getting an error message "too many arguments". Can you tell me what I'm missing? =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Thanks in advance, |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If returns error (too many arguments)
Thank you for the feedback. Don't forget to click YES below. :)
-- John C "plally" wrote: That worked. Thanks so much. Pauline L Lally "John C" wrote: You have re-arranged some of the parentheses. Copy and paste the following formula: =(MAX(MIN(C2,DATE(V1,12,31)),DATE(V1,1,1))-MIN(MAX(B2,DATE(V1,1,1)),DATE(V1,12,31)))/365*AnnPremium Change AnnPremium to whatever cell reference you need to, but the /365 * AnnPremium do not need to be inside any parentheses. -- John C "plally" wrote: Nice Logic. Way above my every-day use of Excel! Anyway, when I insert the formula I get an error relating to "negative dates or times" =MAX(MIN(C2,DATE(V1,12,31),DATE(V1,1,1))-(MIN(MAX(B2,DATE(V1,1,1)),DATE(V1,12,31))/365*U2)) C2=policy end date V1=Prem Year B2=Policy issue date Excel suggested the extra parenthesis'. "John C" wrote: Okay, let's rework the formula. If I understand you correctly, you are wanting to calculate the total premium for a single given year, based on the policy effective date and policy termination date. The Premium date is the year that you are trying to calculate the premium for. You are trying to calculate the number of days in the Premium Year, and then use the number of days of the premium year, divide by 365, then multiply by the Annual premium, and that is your resultant. That being said, why not use a formula like so: =(MAX(MIN(TermYear2,DATE(PremYear,12,31)),DATE(Pre mYear,1,1))-MIN(MAX(EffYear2,DATE(PremYear,1,1)),DATE(PremYear ,12,31)))/365*AnnPremium TermYear2 = actual end date of policy EffYear2 = actual start date of policy PremYear = year you are trying to calculate for. AnnPremium = Annual premium amount. It first determines the earliest date 12/31 of Prem Year, or Termination date (the MIN). Then it takes that and calculates which is later, that or 1/1 of Prem Year. Then it determines the later of 2 dates, the effective date of the policy, or 1/1 of Prem Year, and compares that to which is earliest, that or 12/31 of Prem Year. If TermDate is less than PremYear, both calculations will come up to 1/1 of prem year. If EffDate is greater than PremYear, both calculations will come up to 12/31 of prem year. It ill take the first value and subtract the second value, divide this by 365, and then multiply it by the Annual Premium. Hope this helps. -- John C "plally" wrote: I modified your formula a bit to accomodate my cells. Here is the modified version. When I inserted the six end parenthesis, Excel did not like that and suggested changes to the formula. =IF(OR(R4V3,S4<V3,),0, (IF(AND(R4=V3,S4V3),YEARFRAC(V2-B4)*U4), IF(R4=S4,T4*U4), IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4))) I am now getting the error: #N/A "Roger Govier" wrote: Hi Using some of the explanation for your second posting, and combining it with your first posting, I think that this is what you want. =if(or(R4V3,S4<V3,),0, if(and(R4=V3,S4V3),yearfrac(V2-B4)*U4), if(R4=S4,T4*U4), (((TERM DATE - EFF DATE)/365)/365*ANN PREM), if(and(R4<V3,S4=V3),((C4-V1)/365)*U4)))))) You will need to translate the TERM DATE etc to the relevant cells holding the values. -- Regards Roger Govier "plally" wrote in message ... So, this is what I'm trying to do. I'm trying to calculate premiums (PREM) for a given year (PREM YEAR) based on the policy issue date (EFF DATE) and policy termination date (TERM DATE). I also need to look at policy issue year (EFF YR) and policy termination year (TERM YR). All policies have an annual premium (ANN PREM). If the EFF YR PREM Year or TERM YR < PREM Year, Prem = 0 If EFF YR = PREM YEAR and TERM YR PREM Year, then perform the following Calculation: (((last day of prem yr - effective date)/365)*ANN PREM) If EFF YR = TERM YEAR, perform the following calculation: (((TERM DATE - EFF DATE)/365)/365*ANN PREM) If EFF YR < PREM Year and TERM YR = PREM Year, perform the following calculation: ((TERM DATE - day 1 of Prem Year)/365)*ANN PREM) my formula points to a cell that includes PREM YEAR, Day 1 of Prem Year and Last day of Prem Yr. Thanks for taking a look at this. "John C" wrote: I tried to decipher what you are meaning, but I can't tell where one IF statement ends, and the next begins.... =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Might I suggest writing the formula inside out? in other words, writing your last IF statement first, then modifying it with the 2nd to last. For example: =IF(AND(R4<V3,S4=V3),((C4-V1)/365)*U4,0) : Note, this assumes that the ,0 is for this IF statement. Your statement is difficult to decipher if the ,0 is for this one, or for the original statement. Go Back to the statement, add the next IF statement outside of your first (which I 'think' your last IF statement is if your 2nd to last is negative.... =IF(R4=S4,T4-U4,last if statement) etc. If this doesn't help, let us know what conditions you are testing for, and what the result you want to be is. -- John C "plally" wrote: Here is my If statement. I am getting an error message "too many arguments". Can you tell me what I'm missing? =if(or(R4V3,S4<V3,),(if(and(R4=V3,S4V3),(yearfra c(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0)) Thanks in advance, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Too many arguments for nested IFs | Excel Discussion (Misc queries) | |||
| nested if still has too many arguments | Excel Discussion (Misc queries) | |||
| 3 level nested if has too many arguments ? | Excel Discussion (Misc queries) | |||
| nested if statement returns #value error | Excel Discussion (Misc queries) | |||
| Excel function LARGE returns error 'too few arguments' | Excel Worksheet Functions | |||
Linear Mode
