Hi,
that is fine working, but the next cell having a formula
=IF(I2="S",CEILING(K2*L2,0.05),IF(I2="P",ROUNDDOWN (K2*L2/0.05,0)*0.05,""))
is getting error.
Any solution?
thanks
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=400252

I tried your formula and didn't get any errors. What error are you
getting?


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=400252

Hi Cutter,
Thanks for you quick response, I will explain,my
workbook is made for foreign currency transaction. so, the conversion
on based on mainly 3 cells
I------- J ------ K --- L
--- M
pur/sale--currency---------quantity------rate--------amount

as u know i made a rate table in the other sheet to get in the cell L2.
In the cell M2
a formula
=IF(I2="S",CEILING(K2*L2,0.05),IF(I2="P",ROUNDDOWN (K2*L2/0.05,0)*0.05,""))
is there. When J2 is empty #VALUE message is showing in M2.. So i
have to some adjustment formula on M2. Like If J2 is empty M2 should
be blank. I didn't get the idea. It may be a 3rd If formula. So,
Any suggestion from you or anybody will be highly appreciated.
Thanks and regards
by
NOWFAL


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=400252

OK. I see. When J2 is empty it affects L2.

So to take care of that adjust your formula to either this:

=IF(J2="","",IF(I2="S",CEILING(K2*L2,0.05),IF(I2=" P",ROUNDDOWN(K2*L2/0.05,0)*0.05,"")))

OR this

=IF(J2<"",IF(I2="S",CEILING(K2*L2,0.05),IF(I2="P" ,ROUNDDOWN(K2*L2/0.05,0)*0.05,"")),"")

These assume you want M2 to be blank if J2 is blank


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=400252

Hi Cutter,
Thank you very much, the first one itself much enough .
I didn't test the second one.
Keep help others, that will benefit you with unknown way. May God bless
you.
thanks and regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=400252

You're quite welcome. Always glad to be of some help. Thanks for the
feedback.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=400252

Hi Cutter,
The earlier topic was as i said worked fine, but
suddenly one related problem came. What happened is the the particular
cell have the formula when copying down, i think it carries something
on it, and that created a #VALUE error on my SUMPRODUCT area. When I
tried individually deleted the copied cell the error goes. That mean
evenif that cell is blank but contains something. So thinking of the
better way to be pure blank . Can I have add something on that formula
that makes the cell as early as blank. For further details giving
below the details of macro works to copy the cells to down.

Range("A2:AH10000").Select
Selection.Copy
Range("A3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=True,
_
IconFileName:=False
Range("C2:H2,J2:K2,N2,O2:Q2,T2:V2,Y2:AA2,AD2:AF2") .ClearContents
ActiveWindow.ScrollColumn = 1
Range("D2").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISNA((VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))),"""",VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))"

please have a look and advice me a solution.
thanks with regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=400252