i'm trying to come up with a yearly travel budgeting spreadsheet and
show the expense by month but when the travel duration spans months, i
can't figure out how not to calculate the airfare cost again.

C3 is # of people
D3 is # of weeks
E3 is Cost formula =(C3*$B$36)+(C3*((D3*7)*$B$40))
where $B$36 is airfare cost and $B$40 is daily room & board cost.

These 3 columns are repeated for every month of the year.

If the stay is two months, C3 will be 1, D3 will be 4, giving E3 to be
$3900 (if $B$36 is $2500 and $B$40 is $200. Now, if I put in F3 "1"
and G3 "4", I'll get the same $3900 in H3 but there's no airfare for
that month since it was already charged in the previous month.

Can someone help? I guess I could do a =if(C3=0,... but I would have
to nest the IF statements 12 times for every month in case the travel
stay is 12 months...

Thanks!


--
silky_green
------------------------------------------------------------------------
silky_green's Profile: http://www.excelforum.com/member.php...fo&userid=5304
View this thread: http://www.excelforum.com/showthread...hreadid=574251

silky_green,

I don't really follow what it is you are saying. You seem to be saying that
having a 4 in both G3 & D3 indicates that both months are a contiguous trip
but then what happens if there is a trip going out the last week of one
month and returning the first week of the next month? Both months would
have a 1 in G3 * D3 but only one air fare. Perhaps you would be better to
have another cell - say by inserting a new Column F - indicating if there is
a "stop-over" from the previous month. This would make your present F3 & G3
into G3 & H3 respectively and your second formula could be:

=(G3*$B$36*(F3=""))+(G3*((H3*7)*$B$40))

The (F3="") will evaluate to TRUE or FALSE depending whether or not we have
put an entry in the new F3 to indicate a stop-over. Excel will convert the
TRUE to 1 or the FALSE to 0 when it is used in a calculation. This will
make your first part of the formula, =(G3*$B$36*(F3="")) zero if there is
anything in F3 but it will calculate as normal if F3 has nothing in it.

Incidentally, the because Excel calculates all multiplications, (or
divisions), first, then additions, (or subtractions), the only bracket that
you really need are the ones round the (F3="") so the formula could be
written as:

=G3*$B$36*(F3="")+G3*H3*7*$B$40

A drawback to the above is that if a user enters a space in F3 it will
*look* blank but will of course have something, (a space " " ), in it so
will return a wrong result. It would be better therefore to have:

=G3*$B$36*(F3="No")+G3*H3*7*$B$40

so that anything except No will return FALSE and so will add the air fare
in.


--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"silky_green"
wrote in message
...

i'm trying to come up with a yearly travel budgeting spreadsheet and
show the expense by month but when the travel duration spans months, i
can't figure out how not to calculate the airfare cost again.

C3 is # of people
D3 is # of weeks
E3 is Cost formula =(C3*$B$36)+(C3*((D3*7)*$B$40))
where $B$36 is airfare cost and $B$40 is daily room & board cost.

These 3 columns are repeated for every month of the year.

If the stay is two months, C3 will be 1, D3 will be 4, giving E3 to be
$3900 (if $B$36 is $2500 and $B$40 is $200. Now, if I put in F3 "1"
and G3 "4", I'll get the same $3900 in H3 but there's no airfare for
that month since it was already charged in the previous month.

Can someone help? I guess I could do a =if(C3=0,... but I would have
to nest the IF statements 12 times for every month in case the travel
stay is 12 months...

Thanks!


--
silky_green
------------------------------------------------------------------------
silky_green's Profile:
http://www.excelforum.com/member.php...fo&userid=5304
View this thread: http://www.excelforum.com/showthread...hreadid=574251