Previous Topic Next Topic

Different Calculation Based on Cell Text

Closed
JoshJStride Posts 4 Registration date Monday 17 October 2016 Status Member Last seen 19 October 2016 - 17 Oct 2016 à 14:47
JoshJStride Posts 4 Registration date Monday 17 October 2016 Status Member Last seen 19 October 2016 - 19 Oct 2016 à 11:06
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

Réponse 1 / 2
Mazzaropi Posts 1983 Registration date Monday 16 August 2010 Status Contributor Last seen 24 May 2023 147
17 Oct 2016 à 16:02
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 Posts 4 Registration date Monday 17 October 2016 Status Member Last seen 19 October 2016
17 Oct 2016 à 16:11
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!
JoshJStride Posts 4 Registration date Monday 17 October 2016 Status Member Last seen 19 October 2016
17 Oct 2016 à 16:22
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.
Réponse 2 / 2
Mazzaropi Posts 1983 Registration date Monday 16 August 2010 Status Contributor Last seen 24 May 2023 147
17 Oct 2016 à 19:05
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
JoshJStride Posts 4 Registration date Monday 17 October 2016 Status Member Last seen 19 October 2016
19 Oct 2016 à 11:06
That's cracked it. Amazing! Thank you very much. Now to get down to inputting the data :-(

Similar discussions

Clear cell contents based on value of another cell range using VBA
Tbonekiller -
Tbonekiller -

6 responses
help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Return Seq Values Until Blank value then return different set of seq values
lorieb63 -
TrowaD -

1 response
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

20 responses
How do I change a base location on Instagram?
rebelkids -
38nancy -

8 responses