New To Excel - Blank Cells?? 2

[apepp]

hello all!

...i don't claim to be much good with excel, have very rarely used it but now need some guidance for a small project currently on the go. I work within the print graphics world so haven't really been in a situation to wade through excel, most are supplied from knowledgeable sources...

...i attach a linked screen grab for further help in what i am trying to achieve here:

http://www.zen63258.zen.co.uk/tek/excel.jpg

...columns H and J are where the results end up due to the formula created, column H is calculated from column C and column J is calculated from column E...

...L1 and M1 are included in the formula as part of the calculation required...

...how do I include in the formula to ignore those cells that are blank? I have looked into ISBLANK command but die to my lack of know how haven't had success...

...eventually these descriptions and prices will end up in a page layout application (indesign or quark), so ideally i don't want these to appear at all...

...i'm sure it is pretty easy to fix really, any ideas greatly appreciated!!

Andrew

 

[AnotherHiggins]

something like this:

[COLOR=blue white]=If(IsBlank(C2),"",YourFormula)[/color]

If column C itself is calculated, then the IsBlank might not work for you. In that case you can use this:
[COLOR=blue white]=If(C2="","",YourFormula)[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Hi Andrew:

If I understand you correctly, the following formula in cell H2 should work for you ...

=IF(C2="","",C2*(1+$L$1)+0.05)

There are other situations to consider ... e.g. what do you want if there is an entry of 0 in column C, or if there is a possibility of TEXT entry in column C.

But let us not get ahead of ourselves, first see if what I have posted works for you.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[apepp]

...great, thanks for the helpful posts...

...my formula is now:

Column H - Ex VAT
IF(C4="","",C4*(1+$L$1)+($M$1))

Column J - Inc VAT
IF(E4="","",E4*(1+$L$1)+($M$1))

...yogi, i do have that 'other' situation with regards to text in some cells of column C, if you have a solution to hand that would be great, but as it stands what i have would get me some way to where i need to be...

...i'm looking into calculating column E instead (from C), as it stands this is an old excel file without any calculations at all, apart from my effort and the help from both of you...

...many thanks for the assistance, given stars all round!!

Andrew

 

[yogia]

Hi Andrew:

As I mentioned earlier, you will have to consider two things ...

1) if there is TEXT entry in C4 and you want H4 to be a formula blank ... then you want to use ...

=IF(OR(C4="",ISTEXT(C4)),"",C4*(1+$L$1)+$M$1)

2) you want to have a formula blank in cell H4, if the entry in C4 is 0, is blank, or is TEXT ... in that case formula in cell H4 would be ...

=IF(OR(C4=0,C4="",ISTEXT(C4)),"",C4*(1+$L$1)+$M$1)

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[apepp]

...superb yogi, thanks again!!

Andrew

 

[apepp]

hi yogi!

Lets say i required the text from C4 to also be transposed to H4 (keeping blanks and zero's as blanks if they occur in the list)?

...is there some form of keep as text function?

Andrew

 

[yogia]

Hi Andrew:

In column H, you have a formula that multiplies the entriy in the corresponding cell of column C with (1+$L$1) and then adds to it $M$1.

So if there is a TEXT entry in a cell of column C, what would you like to happen in the corresponding cell of column H. Think it through, and then give me an example of what you want to do.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[apepp]

Hi Yogi,

This a line from excel here:

Col A Col B Col C
0907 *Chair Seat Pads Red No Charge

...so in column H I would like to try and preserve the "No Charge" text...

Many thanks,

Andrew

 

[yogia]

Hi Andrew:

You mean something like ...

=IF(ISTEXT(C4),C4,C4*(1+$L$1)+$M$1)

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com