Calculate 3 figures based on one amount in one cell

Martinw

New Member
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..

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

mvptomlinson

Well-known Member
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.

Last edited:

Martinw

New Member
Fantastic, much appreciated
Martinw

Replies
12
Views
332
Replies
11
Views
448
Replies
6
Views
237
Replies
1
Views
2K
Replies
4
Views
238

1,191,191
Messages
5,985,211
Members
439,947
Latest member
fabiannic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back