Home

IF function

%3CLINGO-SUB%20id%3D%22lingo-sub-908130%22%20slang%3D%22en-US%22%3EIF%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908130%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20help.%3C%2FP%3E%3CP%3EI%20would%20like%20the%20IF%20function%20to%20return%20a%20value%20in%20%C2%A3's%20which%20i%20can%20use%20for%20another%20SUM.%20However%2C%20i%20can%20only%20get%20a%20number%20in%20the%20cell%20which%20is%20not%20seen%20by%20any%20other%20calculations%20as%20a%20number.%20My%20formular%20is%20this%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(G6%26gt%3B%3D6000%2C%22%C2%A3400%22%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-908130%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908141%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908141%22%20slang%3D%22en-US%22%3EHi%2C%20probably%20better%20to%20not%20include%20%22%C2%A3%22%20symbol%20into%20the%20formula%20but%20instead%20format%20the%20cell%20as%20currency.%20Check%20out%20this%20article%20to%20see%20how%20to%20do%20this%3A%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fformat-numbers-as-currency-0a03bb38-1a07-458d-9e30-2b54366bc7a4%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fformat-numbers-as-currency-0a03bb38-1a07-458d-9e30-2b54366bc7a4%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908159%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908159%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3BI%20have%20tried%20it%20both%20ways%20but%20even%20with%20just%20a%20number%20it%20will%20not%20display%20as%20a%20number%20that%20can%20be%20used%20in%20a%20later%20calculation.%3C%2FP%3E%3CP%3EI%20have%20done%20a%20workaround%20using%20the%20%3DVALUE%20but%20requires%20another%20cell%20to%20be%20used.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908163%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908163%22%20slang%3D%22en-US%22%3Eyou%20should%20get%20rid%20of%20the%20%22%3CBR%20%2F%3E%3DIF(G6%26gt%3B%3D6000%2C400%2C%22%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908164%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908164%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F424283%22%20target%3D%22_blank%22%3E%40TheJackal72%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20thing%20to%20do%20is%20to%20change%20the%20format%20of%20the%20said%20cell%20in%20to%20currency.%20Sometimes%20they%20are%20automatically%20set%20to%20Text%20if%20a%20currency%20sign%20is%20used.%20If%20changing%20to%20currency%20is%20not%20working%20then%20there%20can%20be%20some%20characters%20can%20be%20present%20in%20the%20values%20so%20you%20may%20need%20to%20use%20trim%20and%20clean%20formulas%20to%20remove%20these%20characters.%20If%20this%20works%20then%20you%20can%20copy%20this%20new%20column%20and%20paste%20is%20as%20values%20and%20delete%20the%20original%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E
TheJackal72
New Contributor

Please help.

I would like the IF function to return a value in £'s which i can use for another SUM. However, i can only get a number in the cell which is not seen by any other calculations as a number. My formular is this;

 

=IF(G6>=6000,"£400","")

 

 

4 Replies
Hi, probably better to not include "£" symbol into the formula but instead format the cell as currency. Check out this article to see how to do this: https://support.office.com/en-us/article/format-numbers-as-currency-0a03bb38-1a07-458d-9e30-2b54366b...

@PascalKTeam I have tried it both ways but even with just a number it will not display as a number that can be used in a later calculation.

I have done a workaround using the =VALUE but requires another cell to be used.

you should get rid of the "
=IF(G6>=6000,400,"")

@TheJackal72 

First thing to do is to change the format of the said cell in to currency. Sometimes they are automatically set to Text if a currency sign is used. If changing to currency is not working then there can be some characters can be present in the values so you may need to use trim and clean formulas to remove these characters. If this works then you can copy this new column and paste is as values and delete the original column.

Related Conversations
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
Which formula to use
Ramon Haagen in Excel on
2 Replies
Excel If Functions
Mfouad2255 in Excel on
10 Replies
function talking to table storage
donquijote in Compute on
0 Replies
Calculated column help
gopalaraoa in SharePoint on
1 Replies