Beyond my knowledge or impossible please help

NialithGodSlayer

New Member
Joined
Dec 21, 2020
Messages
8
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,467
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
What should happen if D1 is greater than A1 & C1 is 0?
 

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 21, 2020
Messages
8
Platform
  1. Windows
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
Joined
Dec 21, 2020
Messages
8
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 21, 2020
Messages
8
Platform
  1. Windows
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
    Mr Excel.png
    7.2 KB · Views: 7

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

=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
Joined
Dec 21, 2020
Messages
8
Platform
  1. Windows
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
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
Sorry, but I will not be able to try to help you further. Hopefully, someone else will step in. Good luck!
 

NialithGodSlayer

New Member
Joined
Dec 21, 2020
Messages
8
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top