can someone explain this formula?

[oneway]

=VALUE(MID(K29,10,2))

I'm using a templete that was given to me by a vender. When the cells are filled it puts the data in a chart. All other cells work fine but the three that have this formula doesn't.

Thanks

 

[just_jon]

Welcome to MrExcel -- you can find good descriptions of the MID and VALUE functions in Excel's help file, but your formula breaks down as --

MID(K29,10,2) == Grab the MIDdle characters from cell K29's value, begining with the 10th character and going 2 characters in length.

=VALUE(...) == As MID returns a String, the VALUE function is used to convert its argument to the numeric equivalent.

Could also use

=--MID(K29,10,2)

or

=MID(K29,10,2)+0

 

[Zack Barresse]

Hi oneway, welcome to the board!

I know this may not be what you want to hear, but the Help files are really great for things like this. The only other thing you need to understand is that the inner-most parens are worked on first, so the MID function is calculated before the VALUE function. Another good tool is the formula auditor.



-----------------------------------------------------------------------------

The Help Files...

-----------------------------------------------------------------------------

VALUE

See Also

Converts a text string that represents a number to a number.

Syntax

VALUE(text)

Text is the text enclosed in quotation marks or a reference to a cell containing the text you want to convert.

Remarks

• Text can be in any of the constant number, date, or time formats recognized by Microsoft Excel. If text is not in one of these formats, VALUE returns the #VALUE! error value.
• You do not generally need to use the VALUE function in a formula because Excel automatically converts text to numbers as necessary. This function is provided for compatibility with other spreadsheet programs.

-----------------------------------------------------------------------------

MID

See Also

Also applies to:

MIDB

MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

MIDB returns a specific number of characters from a text string, starting at the position you specify, based on the number of bytes you specify. This function is for use with double-byte characters.

Syntax

MID(text,start_num,num_chars)

MIDB(text,start_num,num_bytes)

Text is the text string containing the characters you want to extract.

Start_num is the position of the first character you want to extract in text. The first character in text has start_num 1, and so on.

Num_chars specifies the number of characters you want MID to return from text.

Num_bytes specifies the number of characters you want MIDB to return from text, in bytes.

Remarks

• If start_num is greater than the length of text, MID returns "" (empty text).
• If start_num is less than the length of text, but start_num plus num_chars exceeds the length of text, MID returns the characters up to the end of text.
• If start_num is less than 1, MID returns the #VALUE! error value.
• If num_chars is negative, MID returns the #VALUE! error value.
• If num_bytes is negative, MIDB returns the #VALUE! error value.

[/list]

 

[oneway]

Thanks guys, it make sense now.
I'll try to remember to look in the help files next time, I've only been working with excel for a couple of months so I'm sure I'll have many question.

Thanks again.