Previous Topic Next Topic

Different Calculation Based on Cell Text

Closed
JoshJStride Posts 4 Registration date Monday October 17, 2016 Status Member Last seen October 19, 2016 - Oct 17, 2016 at 02:47 PM
JoshJStride Posts 4 Registration date Monday October 17, 2016 Status Member Last seen October 19, 2016 - Oct 19, 2016 at 11:06 AM
Hello,

I am self-employed and currently doing my taxes. I'm trying to set up my spreadsheet to make it as efficient as possible and am wondering if it's possible to create different calculations based on the text value of another cell.

I have a column (G) for the total value of the expense and then another column (H) for the value of the claim. Depending on the type of expense (D), I would like H to be a calculation of the value in G. This is because (according to my accountant) I can claim various % of the total depending on the type of expense (eg 90% of mobile phone expenses OR 70% of my home broadband cost).

So, I would need a couple of IF formulae for H - ie IF the text in D is "Utilities" then display 70% of value G in H; IF the text in D is "Mobile Phone" then display 90% of value G in H.

Is this possible? Does this sound utterly ridiculous? Any help would be most appreciated.

Many thanks,

Josh

2 responses

Answer 1 / 2
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Oct 17, 2016 at 04:02 PM
Josh, Good afternoon.

Try to use:

H1 --> =IF(D1="Utilities", G1*70%, IF(D1="Mobile Phone", G1*90%, ""))

Is that what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
1
JoshJStride Posts 4 Registration date Monday October 17, 2016 Status Member Last seen October 19, 2016
Oct 17, 2016 at 04:11 PM
That is excellent! Thank you so much!! I was beginning to wonder if it was possible, or worth my time to find out (instead of just doing it manually), but you have really saved me a hell of a lot of time and I am very grateful. Very much appreciate people like yourself who give up their time to help the uninitiated Excel user.

Muito grato!
0
JoshJStride Posts 4 Registration date Monday October 17, 2016 Status Member Last seen October 19, 2016
Oct 17, 2016 at 04:22 PM
I'm sorry - back again...I actually need about 5 different possibilities (like car & internet) but can't see how to add these without invalidating the formula. Again, I know this is probably quite basic, and I apologise, but any help much appreciated.

Thanks again.
0
Answer 2 / 2
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Oct 17, 2016 at 07:05 PM
JoshJStride, Good evening.

I feel happy to help you. :)

When the problem has more possible options the IF function is not the most efficient or better function, to solve the problem.

Use the VLOOKUP function.
It is more efficient and more professional and easy to maintain, as needed.

I created a small example for you.
https://www.sendspace.com/file/27m61e

I used in cell D1 data validation that helps a lot in time to fill in information. You do not have to keep typing it again.

Do you know Data Validation?
https://support.microsoft.com/en-us/office/apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249?ui=en-us&rs=en-us&ad=us

Any doubts call again.
--
Belo Horizonte, Brasil.
Marcílio Lobão
1
JoshJStride Posts 4 Registration date Monday October 17, 2016 Status Member Last seen October 19, 2016
Oct 19, 2016 at 11:06 AM
That's cracked it. Amazing! Thank you very much. Now to get down to inputting the data :-(
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Excel function question
1234ah -
ac3mark -

3 responses
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

20 responses
Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses