Forum home » Delegate support and help forum » Microsoft Excel Training and help » Nesting if

Nesting if

ResolvedVersion 2007

Fri 15 Jul 2011, 10:58 Delegate Shane said...

Shane has attended:
Excel Advanced course
Excel VBA Intro Intermediate course

Nesting if

Hi,

I did the sample question on the grade allocation.xls file and the formula i used in column K is as follows. Just wanted to know if there is a way to convert a text answer into a numerical figure without using a further IF statement?

=B2+C2+D2+E2+F2+IF(I2=10,10,0)

Thanks,
Shane

Fri 15 Jul 2011, 14:33 Trainer Simon said...

RE: nesting if

Hi Shane,

Thank you for your question and welcome to the forum.

The formula you need is:

=sum(b2:f2,I2)

As I2 could contain either text or a number, having I2 in the sum bracket only uses cells that contains numbers and ignores text without a #value message appearing.

I hope this answers your question.

Regards

Simon

Training courses

98.7 % recommend us

More live stats...

Training information:

Excel tip:

Convert Text into Number

Some times numbers maybe imported in as text or you maybe concatenating numbers that form a text string that now are treated, because you had to extract them by Text functions

To convert Text into Number just encase the relevant cell reference or formula in the TEXT function. See Converting American Date to European hint

eg TEXT(Ref) or TEXT(formula)

View all Excel hints and tips

Nesting if

Nesting if

RE: nesting if

Excel articles

Excel tip:

Convert Text into Number