Calculate 3 figures based on one amount in one cell

[Martinw]

I need a formula that allows the following:
A1 - Enter in number of kilometers
B1, C1 & D1 need to show break up of figure in A1 AS Follows
B1 = 200kms (constant)
C1 = 200kms to 800kms
D1 = 800kms plus

e.g.
A1=1565
B1= 200
C1= 600
D1= 765
or
A1=565
B1=200
C1=365
D1='0'
Must not show any negative numbers
Hope you can help..

 

[mvptomlinson]

Hi Martin,

In B1 use:
=IF(A1>200,200,A1)

In C1 use (revised):
=IF(A1>800,600,IF(A1>200,A1-200,0))

In D1 use:
=IF(A1>800,A1-800,0)

Hope that helps.

 

[Martinw]

Fantastic, much appreciated
Martinw