How to stop cells displaying results outside required parameters

[Sai Sari]

Hi,

I need to find a formula that can stop a value being returned once certain peramiters are exceeded.

For eaxmple, a policy is valid for 7 days.
The start date is January 4th, and expiration is Kanuary 10th.

I need to display the number of days used, and the pro rata cost of the policy used.

The problem is regarding policies which start before the required start date resulting in an overstatement in policy value, and days used. (highlighted in red).

Ideally, I need to somehow fix the values returned in the red cells to the total cost of the policy. ie, it has been used, therefore 7 days cost applied, and not eight, and £70 displayed instead of £80.

I hope my explination is clear enough. I have attached a sample data.

Thanks all in advance

Sai

[deadlyduck]

So am I correct if I say that the max period over which the cost is spread is 7 days i.e. if a policy end date- policy start date exceeds 7 days, the fee is still apportioned over 7 days?

If so, try this formula in cell e4:

Please Login or Register  to view this content.

Alternatively, you could limit the days calculation value in cell F4 using this:

Please Login or Register  to view this content.

Note that it's considered less than ideal/ lessens a spreadsheet's flexibility to 'hard code' a number into an Excel formula (i.e. your formula uses the number 7 in cell e4). You might consider typing the 7 into an empty cell and referencing that cell in your revised formula.

[Richard Buttrey]

Hi,

In F4 and copied down

Please Login or Register  to view this content.

Rgds

[Sai Sari]

Hi All,
Thank you all for your input.
Now my boss wants me to process all data with a cut off date of june 22 instead u=of july 22.
This now shows many results as a negative,
Could someone please sugest a way of keeping these negative values as a zero as they have not yet happened???
I have attached a copy sheet with the probloem highlighted in yellow.

Many Thanks

Sai

[Sai Sari]

Hi All,
Thank you all for your input so far
.
Now my boss wants me to process all data with a cut off date of june 22 instead of july 22.
This now shows many results as a negative,
Could someone please sugest a way of keeping these negative values as a zero as they have not yet happened???
I have attached a copy sheet with the probloem highlighted in yellow.

Many Thanks

Sai

[Richard Buttrey]

Hi,

Please Login or Register  to view this content.

HTH