View original (French)

The IF function

Solved
mike the llama Posted messages 361 Status Member -  
mike the llama Posted messages 361 Status Member -
Hello,

I'm working on a Word file where I inserted a table with some formulas, and there's one I'm struggling with:

In one cell, I need the software to use either of these formulas, depending on the value of the number entered in F5:

IF F5<0 =((F3-F5)/(D1+F4))*100
IF F5>0 =((F3)/(D1+F4+F5))*100

Since I'm not a great specialist in formulas, I tried the following workaround...

=IF(F5<0;((F3-F5)/(D1+F4))*100;IF(F5>0;((F3)/(D1+F4+F5))*100))

...but there seems to be a "SYNTAX ERROR".

Could anyone help me?

Thanks,

mtl

Configuration: Windows 7 / Firefox 48.0

3 answers

Answer 1 / 3
C-Claire Posted messages 4562 Registration date   Status Member Last intervention   2 250
 
Hello Mike the llama,

you might be making Excel volunteers work for nothing...
Your current table and formulas are "pure Word".

Either you got the wrong forum, or you're heading towards an Excel table (preferable considering the type of formula desired) that you can link to Word.

--
C-Claire
1
mike the llama Posted messages 361 Status Member 9
 
Hello C-Claire,

The table is extracted from a 3-page Word file that contains a lot of text. I tried inserting an Excel table linked to Word, but it's very impractical in this situation.

My question is about the IF function, which is why I turned to the Excel forum. I didn't think that using this function in Word or Excel would change anything in the formulation of the formula...
0
C-Claire Posted messages 4562 Registration date   Status Member Last intervention   2 250 > mike the llama Posted messages 361 Status Member
 
mike the llama,

Word formulas look like those in Word but there are differences, starting with the language.
In Word, "SI" will be "IF" and 3 arguments are required (If... Else... Then...).

Mod 2: Your formula will therefore look like this:
{=IF(F5>0;((F3)/(D1+F4+F5))*100;{=IF(F5>0;((F3)/(D1+F4+F5))*100;0)})\# "0 %"}

Reminder: the { } are obtained with [CTRL] [F9]. Do not use those from the keyboard and therefore do not copy/paste the formula directly..

C-Claire
0
C-Claire Posted messages 4562 Registration date   Status Member Last intervention   2 250 > C-Claire Posted messages 4562 Registration date   Status Member Last intervention  
 
Oulala... copy/paste... best friend, worst enemy...
Correction :
{=IF(F5<0;((F3+F5)/(D1+F4))*100;{=IF(F5>0;((F3)/(D1+F4+F5))*100;0)}) \# "0 %"}

This is my final word ;-)

C-Claire
0
Answer 2 / 3
Feudora Posted messages 82 Registration date   Status Member Last intervention  
 
I think your code will work better like this:
assuming nothing happens when F5 = 0 .. 

=IF(F5<0,((F3-F5)/(D1+F4))*100,IF(F5>0,((F3)/(D1+F4+F5))*100),"")


Otherwise, with your line: you need to remove your second IF: 
IF(F5<0,((F3-F5)/(D1+F4))*100,((F3)/(D1+F4+F5))*100))

which would give IF F5 < 0 then ((F3-F5)/(D1+F4))*100) ; ELSE (F3)/(D1+F4+F5))*100)
0
Answer 3 / 3
JSmithJ Posted messages 4403 Registration date   Status Contributor Last intervention   728
 
Hello,

try 
=IF(F5<0;((F3-F5)/(D1+F4))*100;((F3)/(D1+F4+F5))*100)

There will be a problem if F5=0 though, because there is no less than or equal/greater than or equal.
--
;-)
0
mike the llama Posted messages 361 Status Member 9
 
Hello and thank you both!

However, I must have missed something because in the 3 cases mentioned, I'm getting a syntax error again...

I will put the table online and get back to you ;)
0
Feudora Posted messages 82 Registration date   Status Member Last intervention   > mike the llama Posted messages 361 Status Member
 
```html 

=SI(F5<0;((F3-F5)/(D1+F4))*100;SI(F5>0;((F3)/(D1+F4+F5))*100;((F3)/(D1+F4))*100))


```
0
mike the llama Posted messages 361 Status Member 9
 
Here, the file is accessible here.
0
JSmithJ Posted messages 4403 Registration date   Status Contributor Last intervention   728
 
I don't understand the table very well and even less the placement of the cells... For example, which one is F5?!
Then, there are many boxes with numbers surrounded by text or symbols → Very bad idea.
0
jc80 Posted messages 5063 Registration date   Status Member Last intervention   1 443 > mike the llama Posted messages 361 Status Member
 
Hello

An Excel sample would have been preferable
Best regards
0