Excel 2007 : Problem with 'left' and 'if' functions

**taimur32** · 08-26-2011, 03:27 AM

Hey all,

I need to show some numbers (which are in billions) in a small table. The table is being fed from other sheets and when consolidated, the number shows up like 4.123456667E+11, while i just need to show the main rounded figure as 412.

So i did a simple 'left(C17,3)' to get the proper figure, but the trouble was some numbers were in the tens of billions (41) while some were in the hundreds (411). For this I did a nestled 'if' function whereby the figure was rounded off to either two or three digits according to year
{The data shows volumetric progression quarter-wise over the course of multiple years, here's the formula entered: =+IF($C$17=2008,LEFT(C19,2),IF($C$17=2009,LEFT(C19,2),IF($C$17=2010,LEFT(C19,3),IF($C$17=2011,LEFT(C19,3),)))) C17 cell has a drop down to select the year }

Lo and behold, i ran into one last problem. 2008 has 1 quarter with the number in the tens of billions (41) while the remaining are in the hundreds (411)

any suggestions on how to solve this?

**zbor** · 08-26-2011, 03:34 AM

Try this: =INT(C19/10^9)

**arthurbr** · 08-26-2011, 03:34 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**taimur32** · 08-26-2011, 03:48 AM

Originally Posted by zbor

Try this: =INT(C19/10^9)

It worked like a charm, thanks a lot bro.

BTW guys that was my 1st post here, looking forward to asking many more questions, and helping wherever i can