Different Calculation Based on Cell Text [Closed]

Report
Posts
4
Registration date
Monday October 17, 2016
Status
Member
Last seen
October 19, 2016
-
Posts
4
Registration date
Monday October 17, 2016
Status
Member
Last seen
October 19, 2016
-
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 replies

Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
136
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4
Registration date
Monday October 17, 2016
Status
Member
Last seen
October 19, 2016

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!
Posts
4
Registration date
Monday October 17, 2016
Status
Member
Last seen
October 19, 2016

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.
Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
136
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.office.com/en-US/article/Apply-data-validation-to-cells-29FECBCC-D1B9-42C1-9D76-EFF3CE5F7249

Any doubts call again.
--
Belo Horizonte, Brasil.
Marcílio Lobão
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4
Registration date
Monday October 17, 2016
Status
Member
Last seen
October 19, 2016

That's cracked it. Amazing! Thank you very much. Now to get down to inputting the data :-(

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!