Report Formula

  • Thread starter Thread starter Ady
  • Start date Start date
A

Ady

Hi There

I have a formula in my report that adds up all the values of each order:
=Sum([ViewedItemTotal])

However I need a cleverer formula than this! I need a formula that will do
the above, but if the total value of the order is less than 10 it needs to
round up to 10. For example if the total order is 7.50 the field needs to
display 10.

Any help would be highly appreciated.

Regards

Ady
 
You could do this with
=Sum(IIF([ViewedItemTotal]<10,10,[ViewedItemTotal]))
Hope this helps.
Fons
 
Hi There

Many thanks for your reply! However it is not quite working as I
anticipated!!! Let me explain better!

My SUM adds up all the order lines and then produces a total. For example:

Part A: £10
Part B: £15
Part C: £12
Total: £37

I then requested help because if the Total was less than £10 it would
display £10. However the problem is that it now rounds up to £10 on every
order line! I only want the SUM total to round up to £10.

How it works now:
Part A: £5 (It thinks this is £10!!!!!)
Part B: £15
Part C: £12
Total: £37

How I need it to work:
Part A: £2.50
Part B: £2
Part C: £2
Total: £10

Sorry to be awkward. Many many many thanks for any help and advice given!

Regards

Ady







Fons Ponsioen said:
You could do this with
=Sum(IIF([ViewedItemTotal]<10,10,[ViewedItemTotal]))
Hope this helps.
Fons
-----Original Message-----
Hi There

I have a formula in my report that adds up all the values of each order:
=Sum([ViewedItemTotal])

However I need a cleverer formula than this! I need a formula that will do
the above, but if the total value of the order is less than 10 it needs to
round up to 10. For example if the total order is 7.50 the field needs to
display 10.

Any help would be highly appreciated.

Regards

Ady


.
 
I am still a bit confused because you changed the numbers
pertaining to A B and C however, I think what you intend
is that the report shows to normal sums for A B and C (as
you already have it and only if the grnadtotal is less
than 10 you want it th show as 10.
I hope thiis is correct.
you can do that with only a minor change from my previous
suggestion. Use the following:
=IIF(Sum([GrandTotal])<10,10,[GrandTotal])
Hope this helps.
Fons
-----Original Message-----
Hi There

Many thanks for your reply! However it is not quite working as I
anticipated!!! Let me explain better!

My SUM adds up all the order lines and then produces a total. For example:

Part A: £10
Part B: £15
Part C: £12
Total: £37

I then requested help because if the Total was less than £10 it would
display £10. However the problem is that it now rounds up to £10 on every
order line! I only want the SUM total to round up to £10.

How it works now:
Part A: £5 (It thinks this is £10!!!!!)
Part B: £15
Part C: £12
Total: £37

How I need it to work:
Part A: £2.50
Part B: £2
Part C: £2
Total: £10

Sorry to be awkward. Many many many thanks for any help and advice given!

Regards

Ady







You could do this with
=Sum(IIF([ViewedItemTotal]<10,10,[ViewedItemTotal]))
Hope this helps.
Fons
-----Original Message-----
Hi There

I have a formula in my report that adds up all the
values
of each order:
=Sum([ViewedItemTotal])

However I need a cleverer formula than this! I need a formula that will do
the above, but if the total value of the order is less than 10 it needs to
round up to 10. For example if the total order is 7.50 the field needs to
display 10.

Any help would be highly appreciated.

Regards

Ady


.


.
 
Hi There

Many thanks for your help, it is really appreciated. We are now even closer
to a solution!

You correctly guessed what I was trying to do. Your formula works great,
thank you very much. But there is one problem. When I place this formula in
the report footer (it is presently in the orderID footer) it calculates
correctly but and ignores the rounding order up to £10.

It is simply adding all the lines up. I have one order that has two lines:
1 line £5.00
2 line £2.00
Your formula is now rounding the total up to £10 (Excellent). Yet when this
formula is placed into the report footer it calculates to £7. Not quite what
I want. I need £10!!

Many, many thanks for your help!

Ady


I am still a bit confused because you changed the numbers
pertaining to A B and C however, I think what you intend
is that the report shows to normal sums for A B and C (as
you already have it and only if the grnadtotal is less
than 10 you want it th show as 10.
I hope thiis is correct.
you can do that with only a minor change from my previous
suggestion. Use the following:
=IIF(Sum([GrandTotal])<10,10,[GrandTotal])
Hope this helps.
Fons
-----Original Message-----
Hi There

Many thanks for your reply! However it is not quite working as I
anticipated!!! Let me explain better!

My SUM adds up all the order lines and then produces a total. For example:

Part A: £10
Part B: £15
Part C: £12
Total: £37

I then requested help because if the Total was less than £10 it would
display £10. However the problem is that it now rounds up to £10 on every
order line! I only want the SUM total to round up to £10.

How it works now:
Part A: £5 (It thinks this is £10!!!!!)
Part B: £15
Part C: £12
Total: £37

How I need it to work:
Part A: £2.50
Part B: £2
Part C: £2
Total: £10

Sorry to be awkward. Many many many thanks for any help and advice given!

Regards

Ady







You could do this with
=Sum(IIF([ViewedItemTotal]<10,10,[ViewedItemTotal]))
Hope this helps.
Fons
-----Original Message-----
Hi There

I have a formula in my report that adds up all the
values
of each order:
=Sum([ViewedItemTotal])

However I need a cleverer formula than this! I need a formula that will do
the above, but if the total value of the order is less than 10 it needs to
round up to 10. For example if the total order is 7.50 the field needs to
display 10.

Any help would be highly appreciated.

Regards

Ady


.


.
 
Ady. Can you give me an example of the numbers as they
are now in the report and than what you would them want to
look like.
Fons

-----Original Message-----
Hi There

Many thanks for your help, it is really appreciated. We are now even closer
to a solution!

You correctly guessed what I was trying to do. Your formula works great,
thank you very much. But there is one problem. When I place this formula in
the report footer (it is presently in the orderID footer) it calculates
correctly but and ignores the rounding order up to £10.

It is simply adding all the lines up. I have one order that has two lines:
1 line £5.00
2 line £2.00
Your formula is now rounding the total up to £10 (Excellent). Yet when this
formula is placed into the report footer it calculates to £7. Not quite what
I want. I need £10!!

Many, many thanks for your help!

Ady


I am still a bit confused because you changed the numbers
pertaining to A B and C however, I think what you intend
is that the report shows to normal sums for A B and C (as
you already have it and only if the grnadtotal is less
than 10 you want it th show as 10.
I hope thiis is correct.
you can do that with only a minor change from my previous
suggestion. Use the following:
=IIF(Sum([GrandTotal])<10,10,[GrandTotal])
Hope this helps.
Fons
-----Original Message-----
Hi There

Many thanks for your reply! However it is not quite working as I
anticipated!!! Let me explain better!

My SUM adds up all the order lines and then produces a total. For example:

Part A: £10
Part B: £15
Part C: £12
Total: £37

I then requested help because if the Total was less than £10 it would
display £10. However the problem is that it now rounds up to £10 on every
order line! I only want the SUM total to round up to £10.

How it works now:
Part A: £5 (It thinks this is £10!!!!!)
Part B: £15
Part C: £12
Total: £37

How I need it to work:
Part A: £2.50
Part B: £2
Part C: £2
Total: £10

Sorry to be awkward. Many many many thanks for any help and advice given!

Regards

Ady







You could do this with
=Sum(IIF([ViewedItemTotal]<10,10,[ViewedItemTotal]))
Hope this helps.
Fons
-----Original Message-----
Hi There

I have a formula in my report that adds up all the values
of each order:
=Sum([ViewedItemTotal])

However I need a cleverer formula than this! I need a
formula that will do
the above, but if the total value of the order is less
than 10 it needs to
round up to 10. For example if the total order is 7.50
the field needs to
display 10.

Any help would be highly appreciated.

Regards

Ady


.


.


.
 
Hi Fons

First of all many thanks for your continued help.

I am getting this at present:

Order 1
Line 1 Total £12
Line 2 Total £10
Line 3 Total £5
Order footer Total: £27 (correct)

Order 2
Line 1 Total £2
Line 2 Total £2
Line 3 Total £1
Order Footer Total: £10 (correct new Formul rounds up to £10)

Total shown in Report footer: £32 (Wrong -
It is not using the formula)
Total I would like to see in the Report footer: £37

I hope you understand what I am trying to achieve. The formula I am using
is:
=IIf(Sum([ViewedItemTotal])<10,10,Sum([ViewedItemTotal]))

Many many thanks for your help!

Regards

Ady











Ady. Can you give me an example of the numbers as they
are now in the report and than what you would them want to
look like.
Fons

-----Original Message-----
Hi There

Many thanks for your help, it is really appreciated. We are now even closer
to a solution!

You correctly guessed what I was trying to do. Your formula works great,
thank you very much. But there is one problem. When I place this formula in
the report footer (it is presently in the orderID footer) it calculates
correctly but and ignores the rounding order up to £10.

It is simply adding all the lines up. I have one order that has two lines:
1 line £5.00
2 line £2.00
Your formula is now rounding the total up to £10 (Excellent). Yet when this
formula is placed into the report footer it calculates to £7. Not quite what
I want. I need £10!!

Many, many thanks for your help!

Ady


I am still a bit confused because you changed the numbers
pertaining to A B and C however, I think what you intend
is that the report shows to normal sums for A B and C (as
you already have it and only if the grnadtotal is less
than 10 you want it th show as 10.
I hope thiis is correct.
you can do that with only a minor change from my previous
suggestion. Use the following:
=IIF(Sum([GrandTotal])<10,10,[GrandTotal])
Hope this helps.
Fons
-----Original Message-----
Hi There

Many thanks for your reply! However it is not quite working as I
anticipated!!! Let me explain better!

My SUM adds up all the order lines and then produces a total. For example:

Part A: £10
Part B: £15
Part C: £12
Total: £37

I then requested help because if the Total was less than £10 it would
display £10. However the problem is that it now rounds up to £10 on every
order line! I only want the SUM total to round up to £10.

How it works now:
Part A: £5 (It thinks this is £10!!!!!)
Part B: £15
Part C: £12
Total: £37

How I need it to work:
Part A: £2.50
Part B: £2
Part C: £2
Total: £10

Sorry to be awkward. Many many many thanks for any help and advice given!

Regards

Ady







You could do this with
=Sum(IIF([ViewedItemTotal]<10,10,[ViewedItemTotal]))
Hope this helps.
Fons
-----Original Message-----
Hi There

I have a formula in my report that adds up all the values
of each order:
=Sum([ViewedItemTotal])

However I need a cleverer formula than this! I need a
formula that will do
the above, but if the total value of the order is less
than 10 it needs to
round up to 10. For example if the total order is 7.50
the field needs to
display 10.

Any help would be highly appreciated.

Regards

Ady


.


.


.
 
Not sure, but I would try adding a control to the order footer Total that does a
running sum overall of your calculation and then refer to that control in your footer

New Control in Order Footer:
Name: AddUp
Control Source: =IIf(Sum([ViewedItemTotal])<10,10,Sum([ViewedItemTotal]))
Running Sum: Over All
Visible: No

New control in the Report footer:
Control Source: =AddUp
Running Sum: No
Visible: Yes
Hi Fons

First of all many thanks for your continued help.

I am getting this at present:

Order 1
Line 1 Total £12
Line 2 Total £10
Line 3 Total £5
Order footer Total: £27 (correct)

Order 2
Line 1 Total £2
Line 2 Total £2
Line 3 Total £1
Order Footer Total: £10 (correct new Formul rounds up to £10)

Total shown in Report footer: £32 (Wrong -
It is not using the formula)
Total I would like to see in the Report footer: £37

I hope you understand what I am trying to achieve. The formula I am using
is:
=IIf(Sum([ViewedItemTotal])<10,10,Sum([ViewedItemTotal]))

Many many thanks for your help!

Regards

Ady

Ady. Can you give me an example of the numbers as they
are now in the report and than what you would them want to
look like.
Fons
-----Original Message-----
Hi There

Many thanks for your help, it is really appreciated. We are now even closer
to a solution!

You correctly guessed what I was trying to do. Your formula works great,
thank you very much. But there is one problem. When I place this formula in
the report footer (it is presently in the orderID footer) it calculates
correctly but and ignores the rounding order up to £10.

It is simply adding all the lines up. I have one order that has two lines:
1 line £5.00
2 line £2.00
Your formula is now rounding the total up to £10 (Excellent). Yet when this
formula is placed into the report footer it calculates to £7. Not quite what
I want. I need £10!!

Many, many thanks for your help!

Ady


I am still a bit confused because you changed the numbers
pertaining to A B and C however, I think what you intend
is that the report shows to normal sums for A B and C (as
you already have it and only if the grnadtotal is less
than 10 you want it th show as 10.
I hope thiis is correct.
you can do that with only a minor change from my previous
suggestion. Use the following:
=IIF(Sum([GrandTotal])<10,10,[GrandTotal])
Hope this helps.
Fons
-----Original Message-----
Hi There

Many thanks for your reply! However it is not quite working as I
anticipated!!! Let me explain better!

My SUM adds up all the order lines and then produces a total. For example:

Part A: £10
Part B: £15
Part C: £12
Total: £37

I then requested help because if the Total was less than £10 it would
display £10. However the problem is that it now rounds up to £10 on every
order line! I only want the SUM total to round up to £10.

How it works now:
Part A: £5 (It thinks this is £10!!!!!)
Part B: £15
Part C: £12
Total: £37

How I need it to work:
Part A: £2.50
Part B: £2
Part C: £2
Total: £10

Sorry to be awkward. Many many many thanks for any help and advice given!

Regards

Ady







You could do this with
=Sum(IIF([ViewedItemTotal]<10,10,[ViewedItemTotal]))
Hope this helps.
Fons
-----Original Message-----
Hi There

I have a formula in my report that adds up all the values
of each order:
=Sum([ViewedItemTotal])

However I need a cleverer formula than this! I need a
formula that will do
the above, but if the total value of the order is less
than 10 it needs to
round up to 10. For example if the total order is 7.50
the field needs to
display 10.

Any help would be highly appreciated.

Regards

Ady


.



.


.
 
This worked!!!!!!

Many, many, many thanks!!!!!!!!!!!!!!!

Ady

John Spencer (MVP) said:
Not sure, but I would try adding a control to the order footer Total that does a
running sum overall of your calculation and then refer to that control in your footer

New Control in Order Footer:
Name: AddUp
Control Source: =IIf(Sum([ViewedItemTotal])<10,10,Sum([ViewedItemTotal]))
Running Sum: Over All
Visible: No

New control in the Report footer:
Control Source: =AddUp
Running Sum: No
Visible: Yes
Hi Fons

First of all many thanks for your continued help.

I am getting this at present:

Order 1
Line 1 Total £12
Line 2 Total £10
Line 3 Total £5
Order footer Total: £27 (correct)

Order 2
Line 1 Total £2
Line 2 Total £2
Line 3 Total £1
Order Footer Total: £10 (correct new Formul rounds up to £10)

Total shown in Report footer: £32 (Wrong -
It is not using the formula)
Total I would like to see in the Report footer: £37

I hope you understand what I am trying to achieve. The formula I am using
is:
=IIf(Sum([ViewedItemTotal])<10,10,Sum([ViewedItemTotal]))

Many many thanks for your help!

Regards

Ady

Ady. Can you give me an example of the numbers as they
are now in the report and than what you would them want to
look like.
Fons
-----Original Message-----
Hi There

Many thanks for your help, it is really appreciated. We are now even closer
to a solution!

You correctly guessed what I was trying to do. Your formula works great,
thank you very much. But there is one problem. When I place this formula in
the report footer (it is presently in the orderID footer) it calculates
correctly but and ignores the rounding order up to £10.

It is simply adding all the lines up. I have one order that has two lines:
1 line £5.00
2 line £2.00
Your formula is now rounding the total up to £10 (Excellent). Yet when this
formula is placed into the report footer it calculates to £7. Not quite what
I want. I need £10!!

Many, many thanks for your help!

Ady


I am still a bit confused because you changed the numbers
pertaining to A B and C however, I think what you intend
is that the report shows to normal sums for A B and C (as
you already have it and only if the grnadtotal is less
than 10 you want it th show as 10.
I hope thiis is correct.
you can do that with only a minor change from my previous
suggestion. Use the following:
=IIF(Sum([GrandTotal])<10,10,[GrandTotal])
Hope this helps.
Fons
-----Original Message-----
Hi There

Many thanks for your reply! However it is not quite
working as I
anticipated!!! Let me explain better!

My SUM adds up all the order lines and then produces a
total. For example:

Part A: £10
Part B: £15
Part C: £12
Total: £37

I then requested help because if the Total was less than
£10 it would
display £10. However the problem is that it now rounds up
to £10 on every
order line! I only want the SUM total to round up to £10.

How it works now:
Part A: £5 (It thinks this is £10!!!!!)
Part B: £15
Part C: £12
Total: £37

How I need it to work:
Part A: £2.50
Part B: £2
Part C: £2
Total: £10

Sorry to be awkward. Many many many thanks for any help
and advice given!

Regards

Ady







"Fons Ponsioen" <[email protected]>
wrote in message
You could do this with
=Sum(IIF([ViewedItemTotal]<10,10,[ViewedItemTotal]))
Hope this helps.
Fons
-----Original Message-----
Hi There

I have a formula in my report that adds up all the
values
of each order:
=Sum([ViewedItemTotal])

However I need a cleverer formula than this! I need a
formula that will do
the above, but if the total value of the order is less
than 10 it needs to
round up to 10. For example if the total order is 7.50
the field needs to
display 10.

Any help would be highly appreciated.

Regards

Ady


.



.



.
 
Back
Top