Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formulae??????????? Excel 2003
Col F contains a purchase amount including a GST tax, col G contains the same
purchase amount excluding GST. Col K is sale price inc GST, col L excluding GST. When ever the value in L is than that of G, I need to calculate the difference between the two amounts of tax, ($2000 at purchase & $19090.91 at sale, being $90.91) I then need to show this figure in col I as well as adding it into col H. F G H I K L $22000.00 $20000.00 $861.91 $90.91 $21000.00 $19090.91 Cheers from frustrated excel novice, Paul |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formulae??????????? Excel 2003
We need to know what is in column H. A formula?
Tyro "Aussie Paul" <Aussie wrote in message ... Col F contains a purchase amount including a GST tax, col G contains the same purchase amount excluding GST. Col K is sale price inc GST, col L excluding GST. When ever the value in L is than that of G, I need to calculate the difference between the two amounts of tax, ($2000 at purchase & $19090.91 at sale, being $90.91) I then need to show this figure in col I as well as adding it into col H. F G H I K L $22000.00 $20000.00 $861.91 $90.91 $21000.00 $19090.91 Cheers from frustrated excel novice, Paul |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formulae??????????? Excel 2003
Hi Tyro, Col H has no formula attached to it.
Paul "Tyro" wrote: We need to know what is in column H. A formula? Tyro "Aussie Paul" <Aussie wrote in message ... Col F contains a purchase amount including a GST tax, col G contains the same purchase amount excluding GST. Col K is sale price inc GST, col L excluding GST. When ever the value in L is than that of G, I need to calculate the difference between the two amounts of tax, ($2000 at purchase & $19090.91 at sale, being $90.91) I then need to show this figure in col I as well as adding it into col H. F G H I K L $22000.00 $20000.00 $861.91 $90.91 $21000.00 $19090.91 Cheers from frustrated excel novice, Paul |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formulae??????????? Excel 2003
Tyro, additional info. Col H is a col for additional cost related to that
purchase. Input manually. The difference between the two tax amounts becomes an additional cost. Cheers Paul "Tyro" wrote: We need to know what is in column H. A formula? Tyro "Aussie Paul" <Aussie wrote in message ... Col F contains a purchase amount including a GST tax, col G contains the same purchase amount excluding GST. Col K is sale price inc GST, col L excluding GST. When ever the value in L is than that of G, I need to calculate the difference between the two amounts of tax, ($2000 at purchase & $19090.91 at sale, being $90.91) I then need to show this figure in col I as well as adding it into col H. F G H I K L $22000.00 $20000.00 $861.91 $90.91 $21000.00 $19090.91 Cheers from frustrated excel novice, Paul |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formulae??????????? Excel 2003
With the numbers shown, and assuming all the numbers are in row 1, the
formula for I1 is simply =F1-G1-K1+L1. (Or =F1-G1-(K1-L1), in case you're wondering where the + came from.) It's column H that presents a problem.You say it contains a manually entered number. The only way (without using VBA) to add the $90.91 to that is to put the answer in another cell such as M1 where the formula would be =H1+I1. The formula for I1 assumes that F1 is greater than or equal to K1. Tyro "Aussie Paul" wrote in message ... Tyro, additional info. Col H is a col for additional cost related to that purchase. Input manually. The difference between the two tax amounts becomes an additional cost. Cheers Paul "Tyro" wrote: We need to know what is in column H. A formula? Tyro "Aussie Paul" <Aussie wrote in message ... Col F contains a purchase amount including a GST tax, col G contains the same purchase amount excluding GST. Col K is sale price inc GST, col L excluding GST. When ever the value in L is than that of G, I need to calculate the difference between the two amounts of tax, ($2000 at purchase & $19090.91 at sale, being $90.91) I then need to show this figure in col I as well as adding it into col H. F G H I K L $22000.00 $20000.00 $861.91 $90.91 $21000.00 $19090.91 Cheers from frustrated excel novice, Paul |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formulae??????????? Excel 2003
Hey Tyro, that formula does calculate the correct amount for me, thank you,
however I need it to only perform that calculation when the sale price is less than the purchase price. At present it calculates regardless of buy or sale price, when applied to the entire column. The addition of this calculation to col H is no big deal, I can easily do it manually. The big pain is doing the other calculation manually and keeping track of when its required. Which is when the sale price is less than the purchase price, regardless of any additional reconditioning costs. Cheers Paul "Tyro" wrote: With the numbers shown, and assuming all the numbers are in row 1, the formula for I1 is simply =F1-G1-K1+L1. (Or =F1-G1-(K1-L1), in case you're wondering where the + came from.) It's column H that presents a problem.You say it contains a manually entered number. The only way (without using VBA) to add the $90.91 to that is to put the answer in another cell such as M1 where the formula would be =H1+I1. The formula for I1 assumes that F1 is greater than or equal to K1. Tyro "Aussie Paul" wrote in message ... Tyro, additional info. Col H is a col for additional cost related to that purchase. Input manually. The difference between the two tax amounts becomes an additional cost. Cheers Paul "Tyro" wrote: We need to know what is in column H. A formula? Tyro "Aussie Paul" <Aussie wrote in message ... Col F contains a purchase amount including a GST tax, col G contains the same purchase amount excluding GST. Col K is sale price inc GST, col L excluding GST. When ever the value in L is than that of G, I need to calculate the difference between the two amounts of tax, ($2000 at purchase & $19090.91 at sale, being $90.91) I then need to show this figure in col I as well as adding it into col H. F G H I K L $22000.00 $20000.00 $861.91 $90.91 $21000.00 $19090.91 Cheers from frustrated excel novice, Paul |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formulae??????????? Excel 2003
Hi Paul,
First up to help others you should have mentioned that GST tax in Australia is a flat rate of 10% across the board. This fomula in I1 will calc the difference between the two tax amounts. =G1*0.1-L1*0.1 Obviously you need more than that but your post is not very clear, e.g. When ever the value in L is than that of G, Is that supposed to be lesser than or greater than? Also what are the row numbers? How is to be added to H? On the next row down maybe? Or is it already added in the $861.91? i.e. was the value above that one $770.00? The better explanation you give the better answer you will get. Regards Martin "Aussie Paul" wrote: Col F contains a purchase amount including a GST tax, col G contains the same purchase amount excluding GST. Col K is sale price inc GST, col L excluding GST. When ever the value in L is than that of G, I need to calculate the difference between the two amounts of tax, ($2000 at purchase & $19090.91 at sale, being $90.91) I then need to show this figure in col I as well as adding it into col H. F G H I K L $22000.00 $20000.00 $861.91 $90.91 $21000.00 $19090.91 Cheers from frustrated excel novice, Paul |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formulae??????????? Excel 2003
OK just saw your next post, still confusing but getting closer.
Maybe this. =IF(L1<G1,G1*0.1-L1*0.1,"") You don't say what happens if L1 is greater than G1 so I left it as a blank. HTH Martin "MartinW" wrote: Hi Paul, First up to help others you should have mentioned that GST tax in Australia is a flat rate of 10% across the board. This fomula in I1 will calc the difference between the two tax amounts. =G1*0.1-L1*0.1 Obviously you need more than that but your post is not very clear, e.g. When ever the value in L is than that of G, Is that supposed to be lesser than or greater than? Also what are the row numbers? How is to be added to H? On the next row down maybe? Or is it already added in the $861.91? i.e. was the value above that one $770.00? The better explanation you give the better answer you will get. Regards Martin "Aussie Paul" wrote: Col F contains a purchase amount including a GST tax, col G contains the same purchase amount excluding GST. Col K is sale price inc GST, col L excluding GST. When ever the value in L is than that of G, I need to calculate the difference between the two amounts of tax, ($2000 at purchase & $19090.91 at sale, being $90.91) I then need to show this figure in col I as well as adding it into col H. F G H I K L $22000.00 $20000.00 $861.91 $90.91 $21000.00 $19090.91 Cheers from frustrated excel novice, Paul |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formulae??????????? Excel 2003
Put the formula in an IF statement. =IF(F1K1,F1-G1-K1+L1,0)
Tyro "Aussie Paul" wrote in message ... Hey Tyro, that formula does calculate the correct amount for me, thank you, however I need it to only perform that calculation when the sale price is less than the purchase price. At present it calculates regardless of buy or sale price, when applied to the entire column. The addition of this calculation to col H is no big deal, I can easily do it manually. The big pain is doing the other calculation manually and keeping track of when it's required. Which is when the sale price is less than the purchase price, regardless of any additional reconditioning costs. Cheers Paul "Tyro" wrote: With the numbers shown, and assuming all the numbers are in row 1, the formula for I1 is simply =F1-G1-K1+L1. (Or =F1-G1-(K1-L1), in case you're wondering where the + came from.) It's column H that presents a problem.You say it contains a manually entered number. The only way (without using VBA) to add the $90.91 to that is to put the answer in another cell such as M1 where the formula would be =H1+I1. The formula for I1 assumes that F1 is greater than or equal to K1. Tyro "Aussie Paul" wrote in message ... Tyro, additional info. Col H is a col for additional cost related to that purchase. Input manually. The difference between the two tax amounts becomes an additional cost. Cheers Paul "Tyro" wrote: We need to know what is in column H. A formula? Tyro "Aussie Paul" <Aussie wrote in message ... Col F contains a purchase amount including a GST tax, col G contains the same purchase amount excluding GST. Col K is sale price inc GST, col L excluding GST. When ever the value in L is than that of G, I need to calculate the difference between the two amounts of tax, ($2000 at purchase & $19090.91 at sale, being $90.91) I then need to show this figure in col I as well as adding it into col H. F G H I K L $22000.00 $20000.00 $861.91 $90.91 $21000.00 $19090.91 Cheers from frustrated excel novice, Paul |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formulae??????????? Excel 2003
Martin, Thank you! You have just saved me me untold headaches. This is the
first time I have used this facility and was endevouring to keep things as concise as possible. I will certainly proof read posts pror to posting, in the future, yes it should have beem less than. Thanks again, have a great weekend. Cheers Paul "MartinW" wrote: OK just saw your next post, still confusing but getting closer. Maybe this. =IF(L1<G1,G1*0.1-L1*0.1,"") You don't say what happens if L1 is greater than G1 so I left it as a blank. HTH Martin "MartinW" wrote: Hi Paul, First up to help others you should have mentioned that GST tax in Australia is a flat rate of 10% across the board. This fomula in I1 will calc the difference between the two tax amounts. =G1*0.1-L1*0.1 Obviously you need more than that but your post is not very clear, e.g. When ever the value in L is than that of G, Is that supposed to be lesser than or greater than? Also what are the row numbers? How is to be added to H? On the next row down maybe? Or is it already added in the $861.91? i.e. was the value above that one $770.00? The better explanation you give the better answer you will get. Regards Martin "Aussie Paul" wrote: Col F contains a purchase amount including a GST tax, col G contains the same purchase amount excluding GST. Col K is sale price inc GST, col L excluding GST. When ever the value in L is than that of G, I need to calculate the difference between the two amounts of tax, ($2000 at purchase & $19090.91 at sale, being $90.91) I then need to show this figure in col I as well as adding it into col H. F G H I K L $22000.00 $20000.00 $861.91 $90.91 $21000.00 $19090.91 Cheers from frustrated excel novice, Paul |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex formulae??????????? Excel 2003
Hey Tyro, Thanks very much, I now realise I gave you very unclear directions
to work with initially. Your formula, works just as well as martins. Thanks again, and have a great weekend too. Cheers Paul "Tyro" wrote: Put the formula in an IF statement. =IF(F1K1,F1-G1-K1+L1,0) Tyro "Aussie Paul" wrote in message ... Hey Tyro, that formula does calculate the correct amount for me, thank you, however I need it to only perform that calculation when the sale price is less than the purchase price. At present it calculates regardless of buy or sale price, when applied to the entire column. The addition of this calculation to col H is no big deal, I can easily do it manually. The big pain is doing the other calculation manually and keeping track of when it's required. Which is when the sale price is less than the purchase price, regardless of any additional reconditioning costs. Cheers Paul "Tyro" wrote: With the numbers shown, and assuming all the numbers are in row 1, the formula for I1 is simply =F1-G1-K1+L1. (Or =F1-G1-(K1-L1), in case you're wondering where the + came from.) It's column H that presents a problem.You say it contains a manually entered number. The only way (without using VBA) to add the $90.91 to that is to put the answer in another cell such as M1 where the formula would be =H1+I1. The formula for I1 assumes that F1 is greater than or equal to K1. Tyro "Aussie Paul" wrote in message ... Tyro, additional info. Col H is a col for additional cost related to that purchase. Input manually. The difference between the two tax amounts becomes an additional cost. Cheers Paul "Tyro" wrote: We need to know what is in column H. A formula? Tyro "Aussie Paul" <Aussie wrote in message ... Col F contains a purchase amount including a GST tax, col G contains the same purchase amount excluding GST. Col K is sale price inc GST, col L excluding GST. When ever the value in L is than that of G, I need to calculate the difference between the two amounts of tax, ($2000 at purchase & $19090.91 at sale, being $90.91) I then need to show this figure in col I as well as adding it into col H. F G H I K L $22000.00 $20000.00 $861.91 $90.91 $21000.00 $19090.91 Cheers from frustrated excel novice, Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to solve a complex problem using Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 - Toggle formulae display ON/OFF on worksheet | Excel Discussion (Misc queries) | |||
Ruby access to an existing Spreadsheet with complex formulae | Excel Discussion (Misc queries) | |||
Where to find complex matrix math add-ins for Excel 2003? | Excel Worksheet Functions | |||
EXCEL 2003 formulae | Excel Discussion (Misc queries) |