Question description:
This user has given permission to use the problem statement for this
blog.
wondering if there is a formula that when the name in one cell changes, the formula in the other cell changes?
for example: working on a calculating cost for harvesting grain crops based on acres, bushels harvested, and bushels harvested over x/bushels per acre.
in cell B3 i want when cell A1 says "wheat harvest" it will subtract 20 from cell B2, but when cell A1 says "corn harvest" i want to subtract 40 from cell B2
is this possible?
thanks
Solved by T. D. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
07/02/2018 - 05:19
Welcome to excel got it pro.
Excelchat Expert
07/02/2018 - 05:19
Yes, it is possible to perform different calculations based on text on a cell.
Excelchat Expert
07/02/2018 - 05:20
It can be done using an if formula. Please tell me details and I would write one for you.
Excelchat Expert
07/02/2018 - 05:22
Is that all?
User
07/02/2018 - 05:22
does that make sense? whatever is in a1 i want b3 to either subtract 20 or 40 from b2
Excelchat Expert
07/02/2018 - 05:23
Hmm, perfectly.
User
07/02/2018 - 05:23
so if it says wheat harvest i want 20 subtracted from b2
User
07/02/2018 - 05:23
and if it is corn harvest subtract 40
Excelchat Expert
07/02/2018 - 05:23
=IF(A1="wheat harvest",B2-20,B2-40) this formula will do the job.
Excelchat Expert
07/02/2018 - 05:24
If formula works like this. Check a logic, if true do this, if false do that.
Excelchat Expert
07/02/2018 - 05:24
Here we are checking A1="wheat harvest".
User
07/02/2018 - 05:25
so what if in a1 gets changed to corn harvest? i need to change to -40
Excelchat Expert
07/02/2018 - 05:25
It will then automatically subtract 40.
Excelchat Expert
07/02/2018 - 05:25
If its corn harvest then the logic is false.
Excelchat Expert
07/02/2018 - 05:26
And if false we have told the formula to perform B2-40.
Excelchat Expert
07/02/2018 - 05:26
The formula is in C5.
Excelchat Expert
07/02/2018 - 05:26
Please change A1 to corn harvest and see the result.
User
07/02/2018 - 05:27
ok what about this... if it is wheat harvest or pea harvest i want it to -20 but if it says corn harvest or milo harvest it -40
Excelchat Expert
07/02/2018 - 05:27
Do you understand or having problem? If so please let me know and I will try another approach of explaining.
User
07/02/2018 - 05:27
i understand but i shoulda asked in more detail
Excelchat Expert
07/02/2018 - 05:28
Okay, then we use another function called AND. I will change the existing formula.
Excelchat Expert
07/02/2018 - 05:29
Since it is only B2-20 or B2-40 we can write the formula as follows.
User
07/02/2018 - 05:31
and then also if b2 if under the 20 or 40 i want the cell to be 0 and not a negative number
Excelchat Expert
07/02/2018 - 05:31
The logic is pea or wheat. the formula is =IF(OR(A1="wheat harvest",A1="pea harvest"),B2-20,B2-40) . And since corn or milo is B2-40, we don't need to check this.
Excelchat Expert
07/02/2018 - 05:32
Would you please share the complete details?
Excelchat Expert
07/02/2018 - 05:32
I mean if there's other conditions then also share those please.
User
07/02/2018 - 05:33
just dont want to be a negative number
Excelchat Expert
07/02/2018 - 05:33
Okay thanks.
Excelchat Expert
07/02/2018 - 05:36
Here you go. now you also won't get negative numbers.
Excelchat Expert
07/02/2018 - 05:36
=IF(AND(B2>20,OR(A1="wheat harvest",A1="pea harvest")),B2-20,IF(B2>40,B2-40,0))
Excelchat Expert
07/02/2018 - 05:36
So this will be the final formula.
Excelchat Expert
07/02/2018 - 05:37
Does it makes sense?
Excelchat Expert
07/02/2018 - 05:37
If not please ask me questions.
User
07/02/2018 - 05:37
thanks that will help alot. yes it is very simple. and makes perfext sense.
Excelchat Expert
07/02/2018 - 05:38
Please click on the END SESSION button then to close the session, after that you will be asked to rate me.
Excelchat Expert
07/02/2018 - 05:38
Please consider leaving a 5 star score.
Excelchat Expert
07/02/2018 - 05:38
Thanks and have a great day!
User
07/02/2018 - 05:39
dito
Excelchat Expert
07/02/2018 - 05:39
A friendly reminder, if you don't close the session I'm stuck here and won't be able to help others.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
