Macro

[nishkrish]

Hi i need to write a macro and have no idea how to do it if some one can show
me form scratch

Ihave different formalus in different column which calculate commission for
agent
Eg : if(d1247>300,"$15",if(d1247>100,"$12","$10"))-(j1247+k1247)

=IF(D1246>1500,"$25",IF(D1246>1000,"$20",IF(D1246>500,"$15","10")))-(J1246+K1246)

this 2 different formula in same column

i want to write a macro so i dont have to use two different formula when i
say certain column is X then its should run 1st formula if i select "y" it
should run 2nd formula.

Any help is appreciated.

 

[Mike H]

hi,

Before attempting to answer your question what is "$15" etc in your formula
because it seems to me that the only correct answer you formula can return is
when none of the conditions evaluate as true (D1246 is less than 500) and the
formula returns J1246+K1246.

If any condition does evaluate as TRUE then it tries to subtract J1246+K1246
from a text value and gives an error.

Mike

 

[nishkrish]

its the formula which calculates amount if the sent amount is lessthen $100
then charge should be $10, so on and so forth.

 

[Roger Govier]

Hi

There is no need for a macro to achieve what you want.
just include another IF clause, testing against your selection of X.
I used Cell A1 to either hold X or not

=IF(A1="X",
IF(D1246>1500,25,
IF(D1246>1000,20,
IF(D1246>500,15,10))),
IF(D1247>300,15,
IF(D1247>100,12,10)))
-(J1246+K1246)

I have split the formula on each IF so you can read it more clearly, and to
prevent the newsreader from breaking it in strange places. It is all one
long formula really.

Note that I have removed the " " around your values. As you had the
formula, it would place text values in the cell, which when you try to
deduct (J1246+K1246) at the end, would produce a #VALUE result.
Format the cell as Currency $ if you wish it to show as Dollar amounts

 

[nishkrish]

Roger

this cannot work for since it has to 2 formulas if a= x then 1st formula
should run and if a=y then 2nd formula should run.

 

[Roger Govier]

Hi
Sorry, typo with row numbers

=IF(A1="X",
IF(D1246>1500,25,
IF(D1246>1000,20,
IF(D1246>500,15,10))),
IF(D1246>300,15,
IF(D1246>100,12,10)))
-(J1246+K1246)

It does run 2 different formulae.
If A1 = X then
IF(D1246>1500,25,
IF(D1246>1000,20,
IF(D1246>500,15,10)))

Else if A1 <>"X" then
IF(D1246>300,15,
IF(D1246>100,12,10))

In both cases take the following from the result
-(J1246+K1246)

If you want a situation where A1 could hold something other than X or Y,
then you could modify the formula to
=IF(A1="X",
IF(D1246>1500,25,
IF(D1246>1000,20,
IF(D1246>500,15,10))),
IF(A1="Y",
IF(D1246>300,15,
IF(D1246>100,12,10)),0))
-(J1246+K1246)

In which case if A1 was neither X nor Y, the only part of the formula that
would be invoked would be
-(J1246+K1246)
--
Regards
Roger Govier

Roger

this cannot work for since it has to 2 formulas if a= x then 1st formula
should run and if a=y then 2nd formula should run.