Need help with formula

[Ellas]

Hello everybody,


My name is Ellas, I have a question about Exel.
I’m just starting to use de complicated features of the program, but apparently I’m not that good whit the formulas.

What I’m trying to create:
In my sheet I made a formula to calculate an amount of money. (In tab G38) So far so good.
It looking like this: =((H35*2)+H36)/(10,2/1,7) It calculates the gas bill of a car driving according to the amount of km.
Also in my sheet a drop down box with “Yes” and “No” (in tab G37) this will indicate is the car is driving with or without car trailer.

If the “Yes” is selected the amount of calculated money should increase with 30%
But how do I translate that in my formula?

There must be a simple way wright?

Greetings
Ellas

 

[jasonb75]

Welcome to the board Ellas!

You're right, there is a simple way, as you're new to this, I'll use the conventional method to make it easier for you to follow.

=IF(G37="Yes",(((H35*2)+H36)/(10,2/1,7))*1.3,((H35*2)+H36)/(10,2/1,7))

Multiplying your original result by 1.3 is the same as adding 30%.

edit:

Looking at your original formula I think you will need a different version.

=IF(G37="Yes";(((H35*2)+H36)/(10,2/1,7))*1.3;((H35*2)+H36)/(10,2/1,7))

no major differences, just that some versions of excel require the use of ; instead of , as a delimiter for the function arguments.

 

[Puertorekinsam]

=(((H35*2)+H36)/(10,2/1,7))*if(G37="Yes",1.3,1)

Hello everybody,


My name is Ellas, I have a question about Exel.
I’m just starting to use de complicated features of the program, but apparently I’m not that good whit the formulas.

What I’m trying to create:
In my sheet I made a formula to calculate an amount of money. (In tab G38) So far so good.
It looking like this: =((H35*2)+H36)/(10,2/1,7) It calculates the gas bill of a car driving according to the amount of km.
Also in my sheet a drop down box with “Yes” and “No” (in tab G37) this will indicate is the car is driving with or without car trailer.

If the “Yes” is selected the amount of calculated money should increase with 30%
But how do I translate that in my formula?

There must be a simple way wright?

Greetings
Ellas

 

[AlanY]

oops, should refresh page before reply, sorry

 

[Ellas]

ADVERTISEMENT

First of all, thanks for the quick respond!

But second, none of the formulas are working if I type them in to the sheet

I’ve been getting the “formula you’ve been typing is containing an error” kind of message. And after that, the sheet won’t show any results.

Clearly I’m doing something wrong here. Maybe a dropdown isn’t the way to go in the fist place. Some kind of checkbox maybe? I wonder… any thoughts on that?

Greetings
Ellas

 

[jasonb75]

Did you try the second formula in my post?

I did go back and edit the post so you might have missed it.

 

[Ellas]

ADVERTISEMENT

Tried it again just now, still the same error.. after the error, the cursor automatically highlight the “1,3” in the formula, so I replaced it whit just a 2. Now it won’t give an error anymore, but is still fails to produce a value.
Maybe it’s because of the dropdown not recognizing the value of the word Yes?

 

[jasonb75]

The dropdown is not the issue, the problem is getting the correct syntax for your version of excel.

See if either of these work,

=(((H35*2)+H36)/(10,2/1,7))*if(G37="Yes";1,3;1)

=IF(G37="Yes";(((H35*2)+H36)/(10,2/1,7))*1,3;((H35*2)+H36)/(10,2/1,7))

If they fail it would help if we knew which country your regional settings are based on.

 

[Ellas]

Nope, none of them are working either. Though they don’t give an actual error, they still not display a value.

One would think there would be a universe Excel code…. How odd
Anyhow, I’m using the Dutch version of Excel 2007 (or sometimes 2010, depends on the computer I’m working on)

 

[jasonb75]

Have you checked the values in H35 and H36.

My guess would be H35 = 0

The variations between versions in different countries are not strictly an excel problem, some countries use a comma instead of a period / dot to separate decimal values, excel uses commas to separate the parts of a formula, which causes the problems you experienced initially, hence the need for the modifications to the formula.