can i apply more

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Can I apply more than one function to the same cell?

Can I apply more than one function to the same cell?

resolvedResolved · Low Priority · Version 2013

Can I apply more than one function to the same cell?

Dear STL Team,

Scenario: Staff have bough a new phone but if the phone is less than £250 they do not pay for it. If it is over £250 they pay the difference.

I have used an IF function which works as follows. "Price">=250, "Price"-250, "No Charge". This returns no charge for all prices below £250 and gives me the difference for all the others. However, there is a twist. Some cells in the price column do not contain a number so return an error message. I can delete the message leaving the cell blank. My question is how then can i create a formula to insert text into the blank cells which does not affect the IF function?

Many thanks

Matthew

RE: Can I apply more than one function to the same cell?

Hi Matthew,

Thank you for your question. Might I suggest, instead of deleting the text entries from the Price column, to place the IF function inside an IFERROR function. This will return a result saying, for example, "Non numeric data" instead of #VALUE. Below is an example:

=IFERROR(IF(A2>=250,A2-250,"No charge"),"Non numeric data")

This would be the simplest solution as it will still reflect the amount payable or say "No charge".

I hope this helps.

Kind regards
Marius Barnard
STL


Thu 7 Jul 2016: Automatically marked as resolved.


 

Excel tip:

Importing Numbers in Excel 2010

Occasionally, when importing data into Excel you find that the some of the imported values are treated as text.

To convert these numbers to actual values, click on an empty cell and press Ctrl+C.

Next, select the range that contains the values you need to change and in the Clipboard Group on the Home tab, click the Paste drop-down arrow and choose Paste Special. In the Paste Special dialog box, select Add and then click OK.

View all Excel hints and tips


Server loaded in 0.06 secs.