Nested If returns error (too many arguments)

[plally]

Here is my If statement. I am getting an error message "too many arguments".
Can you tell me what I'm missing?

=if(or(R4>V3,S4<V3,),(if(and(R4=V3,S4>V3),(yearfrac(V2-B4)*U4),if(R4=S4,T4*U4),if(and(R4<V3,S4=V3),(((C4-V1)/365)*U4),0))

Thanks in advance,

 

[John C]

I tried to decipher what you are meaning, but I can't tell where one IF
statement ends, and the next begins...
=if(or(R4>V3,S4<V3,),(if(and(R4=V3,S4>V3),(yearfrac(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.

 

[plally]

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.

 

[plally]

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(R4>V3,S4<V3,),0,
(IF(AND(R4=V3,S4>V3),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

 

[John C]

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(PremYear,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.

 

[plally]

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'.

 

[Roger Govier]

Hi

Yes, I got carried away with the parentheses at the end.
Should have been

=IF(OR(R4>V3,S4<V3),0,
IF(AND(R4=V3,S4>V3),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!!!

 

[John C]

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.

 

[plally]

That worked. Thanks so much.

Pauline L Lally

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]

Thank you for the feedback. Don't forget to click YES below.