Calculation when cell contains a word

[vasbyt]

Hello,

I am trying to do a calculation whereby the sum of two cells would be affected by a word in another cell.

For example, "=I10-G20" let's say equals $50.00. Then if a cell on the same line has the word "hello" I want to minus a fee that the word "hello" imposes on the sum I calculated. So I am trying to subtract a say, $0.0000221 from the sum that =I10-G20 calculated if the word "hello" is in cell N.

Any idea how to do this?

[Pete_UK]

Welcome to the forum.

You can use something like this:

=IF(OR(I10="",G20=""),"",I10-G20-IF(N20="hello",0.0000221,0))

It would help in future if you attached a sample Excel workbook, so we can see the problem in context. The yellow banner at the top of the screen gives details of how to do this.

Hope this helps.

Pete

[vasbyt]

Hi Pete,

Thanks for the response. Absolutely appreciate it. So I tried that and got the same answer as I originally had, but in the negative.

So here is the scenerio. So I do a little trading. Company YYYYY charges me the following fees for every Sell I do:

$0.0000221 *Total $ Trade
$0.000119 * Total Trade Volume

I have attached my excel file. So you if you look at the first charge, $0.0000221 * Total $ Trade which is column (J3). I get charged $0.0160888 for the $728 worth of trades, and 200*0.000119 for the volume (F3)of trades I sold which is 200 amounting to $0.0238. I cannot figure out how to have excel create these two calculations if cell N3 shows YYYYYY.

I am supposed to land up with $57.96, not $58.00, since YYYYYY charges me $0.0160888 from J3 (Total Trade), and $0.0238 from F3 (Total Trade Volume or Shares Sold). So in the end, cell L3 is suppose to show a total of $57.96

I hope this makes sense. Any suggestions?

Cheers!

[bebo021999]

Try:

Please Login or Register  to view this content.

[vasbyt]

Thanks, Bebo. Almost there. The answer I get is $57.93, but I should get $57.96, so it's off by 3 cents.

[JeteMc]

When I put bebo's formula into cell L2 and drag the fill handle down to cell L3 the value 57.96 is displayed.
Let us know if you have any questions.

[vasbyt]

JeteMc.

Yeah, it works if there is something other than YYYYYY in L2. The N2="YYYYYY" is the issue.

I have attached a new sheet for you to see what I mean using Investor 1 and Investor 2 as examples. The answer should be -$2.78

It is not recognizing that only when N2 = Investor 1, only then should it do the calculation.

Play around with it and you will see what I mean.

[JeteMc]

Please take a look at the formulas in cells L2:L3 (file attached to post #7) as they are different.
I believe that if you'll drag the from in cell L2 down to cell L3 you'll see that the formula is conditional to the value in the trade account column.
Let us know if you have any questions.

[vasbyt]

Awesome! Thanks! That worked!! You guys rock!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.