Help with function/formula please!!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ssuzs - 13 Jun 2007 03:26 GMT

Hi! I am trying to write a formula that will calculate sales tax and add
shipping charges and I am not sure how to go about it.
I need the whole column formatted this way.
Example:
F4 is 24.95
I need E4 to be equal to 24.95 + 7.99 (shipping) +8.25% sales tax.

I have one for calculating commission but how do I apply it to the entire
column?
Currently it is =SUM(F7)/4
If you have a better suggestion for that one, I would appreciate it.
Example:
F4 is 24.95
G4 should reflect a 25% commission of that so 6.24 would be the entry in G4.

Please help!!!
Thanks soooo much!
Sabrina

Reply to this Message

T. Valko - 13 Jun 2007 04:15 GMT

>I need E4 to be equal to 24.95 + 7.99 (shipping) +8.25% sales tax.

Do you want to apply the tax against 24.95 or (24.95+7.99)?

To apply the tax against just the 24.95:

=IF(F4="",0,ROUND(F4*1.0825,2)+7.99)

To apply the tax against 24.95+7.99:

=IF(F4="",0,ROUND((F4+7.99)*1.0825,2))

>F4 is 24.95
>G4 should reflect a 25% commission of that so 6.24 would be the entry in
>G4.

=ROUND(F4/4,2)

Biff

> Hi! I am trying to write a formula that will calculate sales tax and add
> shipping charges and I am not sure how to go about it.
[quoted text clipped - 15 lines]
> Thanks soooo much!
> Sabrina

Reply to this Message

Ssuzs - 13 Jun 2007 05:09 GMT

It didn't work. It gave me a number that was close to what it should be but
not the real total. Thank you so much for trying!!! I welcome all suggestions!
Sabrina

> >I need E4 to be equal to 24.95 + 7.99 (shipping) +8.25% sales tax.
>
[quoted text clipped - 35 lines]
> > Thanks soooo much!
> > Sabrina

Reply to this Message

Rick Rothstein (MVP - VB) - 13 Jun 2007 05:45 GMT

Can you give us an example of a value in F4 for which the formula didn't
give the correct answer? And be sure to tell us what you think the correct
answer should have been.

Rick

> It didn't work. It gave me a number that was close to what it should be
> but
[quoted text clipped - 44 lines]
>> > Thanks soooo much!
>> > Sabrina

Reply to this Message

Ssuzs - 13 Jun 2007 06:08 GMT

Sure! The vital information is this:
Column E should be the TOTAL order amount (Cost+S&H+Tax)
Column F should be the COST of the original item purchased (NO tax, NO s&H)
Column G should be the COMMISSION EARNED for the transaction, which is 25%
of the F (COST).

So if someone purchases a product for 24.95
a. I need a formula that will calculate the commission, which in this case,
should be 6.24
b. I need one that will calculate the TOTAL order amount. So 24.95+7.99 with
8.25% sales tax added to that sum. That total in this example should be 35.66

AND I AM A BIG JERK. It does work....THANKS BIFF!!!! I had my S&H entered as
6.95 and it is supposed to be 7.99. I AM SOOOO SORRY.

But how do I apply that formula (=IF(F4="",0,ROUND((F4+7.99)*1.0825,2)) to
all the transactions, not just the F4 transaction?

> Can you give us an example of a value in F4 for which the formula didn't
> give the correct answer? And be sure to tell us what you think the correct
[quoted text clipped - 50 lines]
> >> > Thanks soooo much!
> >> > Sabrina

Reply to this Message

T. Valko - 13 Jun 2007 06:24 GMT

>But how do I apply that formula (=IF(F4="",0,ROUND((F4+7.99)*1.0825,2)) to
>all the transactions, not just the F4 transaction?

Well, if you have a range of price cells, like F4:F10 :

=IF(COUNT(F4:F10),ROUND((SUM(F4:F10)+7.99)*1.0825,2),0)

That assumes S&H is charged for the entire order and not each individual
item.

biff

> Sure! The vital information is this:
> Column E should be the TOTAL order amount (Cost+S&H+Tax)
[quoted text clipped - 77 lines]
>> >> > Thanks soooo much!
>> >> > Sabrina

Reply to this Message

T. Valko - 13 Jun 2007 06:02 GMT

Both formulas return the correct *rounded* results based on your sample
numbers. Perhaps you should let us know what result you expect.

Biff

> It didn't work. It gave me a number that was close to what it should be
> but
[quoted text clipped - 44 lines]
>> > Thanks soooo much!
>> > Sabrina

Reply to this Message

David Biddulph - 13 Jun 2007 08:31 GMT

If you want to calculate F7/4, then use =F7/4. You don't need SUM().
SUM is a function for adding a number of values.

Signature

David Biddulph

...

> I have one for calculating commission but how do I apply it to the entire
> column?
[quoted text clipped - 4 lines]
> G4 should reflect a 25% commission of that so 6.24 would be the entry in
> G4.

...

Reply to this Message