Report

Different Calculation Based on Cell Text

Ask a question JoshJStride 4Posts Monday October 17, 2016Registration date October 19, 2016 Last seen - Last answered on Oct 19, 2016 11:06AM
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
See more 
Helpful
+1
moins plus
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
JoshJStride 4Posts Monday October 17, 2016Registration date October 19, 2016 Last seen - Oct 17, 2016 04:11PM
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!
Reply
JoshJStride 4Posts Monday October 17, 2016Registration date October 19, 2016 Last seen - Oct 17, 2016 04:22PM
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.
Reply
Add comment
Helpful
+1
moins plus
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
JoshJStride 4Posts Monday October 17, 2016Registration date October 19, 2016 Last seen - Oct 19, 2016 11:06AM
That's cracked it. Amazing! Thank you very much. Now to get down to inputting the data :-(
Reply
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!