#### NialithGodSlayer

##### New Member
I will explain the cells first
A1 = a set value (5700)
B1 = deducted value
C1 = cost
D1 = a different deducted value

So i want B1 to be A1-C1 but if the value of C1 is 0 then could i instead get b1 to equal the value of D1 if the value of D1 is less than the value of A1

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
What should happen if D1 is greater than A1 & C1 is 0?

#### joeu2004

##### Banned user
i want B1 to be A1-C1 but if the value of C1 is 0 then could i instead get b1 to equal the value of D1 if the value of D1 is less than the value of A1

I think you are saying: when C1=0, B1 should be the lesser of A1 and D1, since B1 would be A1 otherwise. To that end, the formula in B1 might be:

Excel Formula:
``=IF(C1=0, MIN(A1,D1), A1-C1)``

Last edited by a moderator:

#### NialithGodSlayer

##### New Member
I think you are saying: when C1=0, B1 should be the lesser of A1 and D1, since B1 would be A1 otherwise. To that end, the formula in B1 might be:

Excel Formula:
``=IF(C1=0, MIN(A1,D1), A1-C1)``
you sir are a legend i spent 8hrs trying to get something to work, I'm self taught by trial and error so sometimes its just over my head but thanks what a legend

#### NialithGodSlayer

##### New Member

you sir are a legend i spent 8hrs trying to get something to work, I'm self taught by trial and error so sometimes its just over my head but thanks what a legend
ah no sorry it didnt work in some occasions

#### NialithGodSlayer

##### New Member
Hi & welcome to MrExcel.
What should happen if D1 is greater than A1 & C1 is 0?
So if the value of c1 is 0 i want it to ignore the original formula and just replace the value of b1 with the value of d1 even if d1 is 0, if that makes sense

#### Attachments

• Mr Excel.png
7.2 KB · Views: 7

#### joeu2004

##### Banned user

=IF(C1=0, MIN(A1,D1), A1-C1)
ah no sorry it didnt work in some occasions
So if the value of c1 is 0 i want it to ignore the original formula and just replace the value of b1 with the value of d1 even if d1 is 0

Based on that simplifying requirement, simply write:
Excel Formula:
``=IF(C1=0, D1, A1-C1)``

But I do not see an example where that formula returns anything different from my first formula above, albeit no longer necessary.

Book1
ABCDEF
157005000700100017003000
2570010000100017003000
3570000027003000
4570057000570000
5570000000
6
757005000700100017003000
8570010000100017003000
9570000027003000
10570057000570000
11570000000
Sheet1
Excel Formula:
``````Formulas:
B1: =IF(C1=0, D1, A1-C1)
B7: =IF(C7=0, MIN(A7,D7), A7-C7)
Copy B1 into B2:B5, B7 into B8:B11``````

Note that B1:B5 are the same as B7:B11.

However, note that D4 is 5700, not zero as it appears in your image, because in the image, you explain that ``d1 is a1-e1-f1``.

So in row 5, I entered 0 into D5 instead of the formula. But now note that B5 is zero, not 5700 as it appears in your image.

So I wonder if you meant to say that the rule is:

``if the value of c1 is 0 i want it to ignore the original formula [A1-C1] and just [return] the value of d1 unless [instead of "even"] if d1:f1 are all 0, in which case I want A1.``

If that is the case, the formula in B1 (copied into B2:B5) should be:
Rich (BB code):
``=IF(C1<>0, A1-C1, IF(COUNTIF(D1:F1,0)=3, A1, D1))``

The is demonstrated below.

beyond knowledgeg.xlsx
ABCDEF
1357005000700100017003000
14570010000100017003000
15570000027003000
16570057000570000
17570057000000
Sheet1

#### NialithGodSlayer

##### New Member
Based on that simplifying requirement, simply write:
Excel Formula:
``=IF(C1=0, D1, A1-C1)``

But I do not see an example where that formula returns anything different from my first formula above, albeit no longer necessary.

Book1
ABCDEF
157005000700100017003000
2570010000100017003000
3570000027003000
4570057000570000
5570000000
6
757005000700100017003000
8570010000100017003000
9570000027003000
10570057000570000
11570000000
Sheet1
Excel Formula:
``````Formulas:
B1: =IF(C1=0, D1, A1-C1)
B7: =IF(C7=0, MIN(A7,D7), A7-C7)
Copy B1 into B2:B5, B7 into B8:B11``````

Note that B1:B5 are the same as B7:B11.

However, note that D4 is 5700, not zero as it appears in your image, because in the image, you explain that ``d1 is a1-e1-f1``.

So in row 5, I entered 0 into D5 instead of the formula. But now note that B5 is zero, not 5700 as it appears in your image.

So I wonder if you meant to say that the rule is:

``if the value of c1 is 0 i want it to ignore the original formula [A1-C1] and just [return] the value of d1 unless [instead of "even"] if d1:f1 are all 0, in which case I want A1.``

If that is the case, the formula in B1 (copied into B2:B5) should be:
Rich (BB code):
``=IF(C1<>0, A1-C1, IF(COUNTIF(D1:F1,0)=3, A1, D1))``

The is demonstrated below.

beyond knowledgeg.xlsx
ABCDEF
1357005000700100017003000
14570010000100017003000
15570000027003000
16570057000570000
17570057000000
Sheet1
yes im sorry your formula works just not what i wanted because im not explaining myself well enough
b1 = a1-c1
if the value of b1 is o after a1-c1 thats fine it ends there
if the vaule of b1 is equal to a1 then i want it to put the value of d1 into b1
if the value of b1 is 0 after putting the value of d1 into it
then i want it to put the value of a1 into b1

so sorry for being a pain

#### joeu2004

##### Banned user
Sorry, but I will not be able to try to help you further. Hopefully, someone else will step in. Good luck!

#### NialithGodSlayer

##### New Member
So if the value of c1 is 0 i want it to ignore the original formula and just replace the value of b1 with the value of d1 even if d1 is 0, if that makes sense
b1 = a1-c1
if the value of b1 is o after a1-c1 thats fine it ends there
if the vaule of b1 is equal to a1 then i want it to put the value of d1 into b1
if the value of b1 is 0 after putting the value of d1 into it
then i want it to put the value of a1 into b1

so sorry for being a pain

Replies
15
Views
196
Replies
12
Views
259
Replies
5
Views
207
Replies
4
Views
91
Replies
1
Views
95

1,141,479
Messages
5,706,621
Members
421,460
Latest member
Taamrak

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

### Which adblocker are you using?

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