Optimum Calculation using VBA

I have developed a file to advise me when I should rise the weight of a
airfreight shipment  to receive the best rates available. (It's still
quite rudimentary at this stage)

How it works is like this...let's assume anything up to 45 kgs will
cost  $4.70 per kg, anything equal or above 45 kgs will cost $4.00 per
kg. If we consider that 45 kgs X $4.00 costs $180.00, then any shipment
above 38 kgs at $4.70 per kg will cost more (39 kgs X $4.70 will cost
$183.33, nothing less than a whole kg is valid). Therefore, it is best
to raise the shipment weight to 45 kgs to avoid paying undue shipping
costs.

What I would like to do is make a custom function add-in such as I saw
on this site at
http://www.exceltip.com/st/Build_an_Excel_Add-In_in_Microsoft_Excel/633.html,
so that when I select Insert Formula, I can enter the relevant
information and the VBA will do the rest.

One catch...I want a pop-up comment or something similar to advise me
when to raise the shipment rate rather than a separate cell as  you see
on my attached file. The reason is that  I may not want to change the
weight depending on the circumstances, I just want to know when the
opportunity arises.

Another thing to be considered is that there is a minimum charge,
(let's say $80.00) so any shipment less than 18 kgs will incur this
minimum charge (17 kgs X $4.70 = $79.90). This should change the
airfreight amount automatically, but I still wish to notified so I
understand what has happened.

I am still learning basic VBA, but from I understand of how it works, I
think what I want can be programmed. If anyone can show me how to
achieve this, this will assist me greatly as a reference for future
projects. Thanks very much.

  Attachment filename: airfreight rates optimum calculator.xls  
Download attachment: http://www.excelforum.com/attachment.php?postid=403794
---
Message posted from http://www.ExcelForum.com/

0
1/11/2004 8:34:49 AM
excel.misc 78881 articles. 5 followers. Follow

13 Replies
673 Views

Similar Articles

[PageSpeed] 31

One way. You could also use lookup
ONE LINE
=IF(F19>=45,F19*4,IF(F19*4.7<45*4,F19*4.7,DOLLAR(F19*4.7,2)&" Raise to 45lbs
@$180"))
-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Andrew >" <<Andrew.zuzjz@excelforum-nospam.com> wrote in message
news:Andrew.zuzjz@excelforum-nospam.com...
> I have developed a file to advise me when I should rise the weight of a
> airfreight shipment  to receive the best rates available. (It's still
> quite rudimentary at this stage)
>
> How it works is like this...let's assume anything up to 45 kgs will
> cost  $4.70 per kg, anything equal or above 45 kgs will cost $4.00 per
> kg. If we consider that 45 kgs X $4.00 costs $180.00, then any shipment
> above 38 kgs at $4.70 per kg will cost more (39 kgs X $4.70 will cost
> $183.33, nothing less than a whole kg is valid). Therefore, it is best
> to raise the shipment weight to 45 kgs to avoid paying undue shipping
> costs.
>
> What I would like to do is make a custom function add-in such as I saw
> on this site at
>
http://www.exceltip.com/st/Build_an_Excel_Add-In_in_Microsoft_Excel/633.html,
> so that when I select Insert Formula, I can enter the relevant
> information and the VBA will do the rest.
>
> One catch...I want a pop-up comment or something similar to advise me
> when to raise the shipment rate rather than a separate cell as  you see
> on my attached file. The reason is that  I may not want to change the
> weight depending on the circumstances, I just want to know when the
> opportunity arises.
>
> Another thing to be considered is that there is a minimum charge,
> (let's say $80.00) so any shipment less than 18 kgs will incur this
> minimum charge (17 kgs X $4.70 = $79.90). This should change the
> airfreight amount automatically, but I still wish to notified so I
> understand what has happened.
>
> I am still learning basic VBA, but from I understand of how it works, I
> think what I want can be programmed. If anyone can show me how to
> achieve this, this will assist me greatly as a reference for future
> projects. Thanks very much.
>
>   Attachment filename: airfreight rates optimum calculator.xls
> Download attachment:
http://www.excelforum.com/attachment.php?postid=403794
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Don
1/11/2004 12:54:38 PM
Thanks. On my attached file I used LOOKUP to work out the actual
airfreight, and then IF to display a message in another cell to advise
raising the airfreight if the situation demanded. (I want to be
notified rather than just automatically increasing the airfreight
because a) I want the choice to go ahead before anything is done, and
b) I need to know what has taken place so I make a note of the change)

The LOOKUP calculation used to determine the airfreight itself was
straightforward, determining optimums was calculated as below.

=IF(Weight>Limit3,"",IF(AND(Weight>Limit2,Weight<Limit3,Weight*Rate3>Limit3*Rate4),"Raise
Weight to Limit
3",IF(AND(Weight>Limit1,Weight<Limit2,Weight*Rate2>Limit2*Rate3),"Raise
Weight to Limit
2",IF(AND(Weight>Base,Weight<Limit1,Weight*Rate1>Limit1*Rate2),"Raise
Weight to Limit 1",IF(Weight*Rate<Minimum,"Changed to Minimum","")))))

Weight = Weight Shipped
Rate = Calculable Rate (determined by LOOKUP)
Airfreight = Airfreight Charged
=IF(Weight*Rate<Minimum,Minimum,Weight*Rate)

Base = 0 kgs
Limit1 = 45 kgs
Limit2 = 100 kgs
Limit3 = 500 kgs

Rate1 = $4.70
Rate2 = $4.00
Rate3 = $3.30
Rate4 = $260

Trouble is I may have several shipments listed in the same table. I
could have a Display cell in the same row to advise optimizing, but I
would prefer not to do this and use an automatic pop-up comment
appearing in the corner of the calculated airfreight cell instead. Once
alerted, I can manually increase the weight if I choose to.

Seems to me that this could be done using VBA using "AddComment" if the
right criteria are met using "If" or "Case Is".


---
Message posted from http://www.ExcelForum.com/

0
1/11/2004 4:28:11 PM
Perhaps this is on the right track...(Sorry, I'm just a newbie)

Function Optimum(Weight As Integer)
' This function is to provide advice for raising weight to receive bes
airfreight rates possible
' by the means of a comment being automatically displayed if a les
than optimum weight is entered.
' It also advises when the Minimum Rate will be charged.
' Below 45 kgs      $4.70 per kg
' Below 100 kgs     $4.00 per kg
' Below 500 kgs     $3.30 per kg
' Above 500 kgs     $2.60 per kg
' Minimum Rate      $80.00
If Weight = 0 Then
Optimum = 0
Else
Select Case Weight > 500
Optimum = 0
Case Is >= 100, Is < 500, Weight * 3.3 > 500 * 2.6
ActiveCell.AddComment
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Text Text:="Raise Weight to Limit 3"
ActiveCell.Select
Case Is >= 45, Is < 100, Weight * 4 > 100 * 3.3
ActiveCell.AddComment
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Text Text:="Raise Weight to Limit 2"
ActiveCell.Select
Case Is < 45, Weight * 4.7 > 45 * 400, Weight * 4.7 > 80
ActiveCell.AddComment
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Text Text:="Raise Weight to Limit 1"
ActiveCell.Select
Case Is < 80
ActiveCell.AddComment
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Text Text:="Minimum Charged"
ActiveCell.Select
End Select
End If
End Function

As you can see, I don't know enough VBA to program properly yet. An
help you can give me would be appreciated.

No greater joy than learning a new trick

--
Message posted from http://www.ExcelForum.com

0
1/12/2004 5:27:39 AM
I suppose I could always use Conditional Formatting and color code it to
mean different things.

But really I still want to know how to get a custom message via a
comment using VBA .

There is a beginner's VBA programming book due in the mail...can I wait
that long? Aaaaarrrrrgggghhhh!


---
Message posted from http://www.ExcelForum.com/

0
1/12/2004 10:32:00 AM
C'mon guys...surely one of you MVPs out there must know what I'
after....

I'll write you into my will. You can have my entire collection of rar
and exotic stamps (I promise to go out and buy a couple the day afte
tomorrow) :

--
Message posted from http://www.ExcelForum.com

0
1/12/2004 2:36:59 PM
set up a table like this
            B   C     D
      0.00 4.70 0.00
      45.00 4.00 180.00
      100.00 3.30 330.00
      500.00 2.60 1300.00


then the one line formula below from 44 will produce 180
      44.00
      180.00
=MIN(INDEX($B$9:$D$12,MATCH($A$9,$B$9:$B12),2)*$A$9,INDEX($B$9:$D$12,MATCH($
A$9,$B$9:$B12)+1,3))
-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Andrew >" <<Andrew.zwlk1@excelforum-nospam.com> wrote in message
news:Andrew.zwlk1@excelforum-nospam.com...
> Perhaps this is on the right track...(Sorry, I'm just a newbie)
>
> Function Optimum(Weight As Integer)
> ' This function is to provide advice for raising weight to receive best
> airfreight rates possible
> ' by the means of a comment being automatically displayed if a less
> than optimum weight is entered.
> ' It also advises when the Minimum Rate will be charged.
> ' Below 45 kgs      $4.70 per kg
> ' Below 100 kgs     $4.00 per kg
> ' Below 500 kgs     $3.30 per kg
> ' Above 500 kgs     $2.60 per kg
> ' Minimum Rate      $80.00
> If Weight = 0 Then
> Optimum = 0
> Else
> Select Case Weight > 500
> Optimum = 0
> Case Is >= 100, Is < 500, Weight * 3.3 > 500 * 2.6
> ActiveCell.AddComment
> ActiveCell.Comment.Visible = True
> ActiveCell.Comment.Text Text:="Raise Weight to Limit 3"
> ActiveCell.Select
> Case Is >= 45, Is < 100, Weight * 4 > 100 * 3.3
> ActiveCell.AddComment
> ActiveCell.Comment.Visible = True
> ActiveCell.Comment.Text Text:="Raise Weight to Limit 2"
> ActiveCell.Select
> Case Is < 45, Weight * 4.7 > 45 * 400, Weight * 4.7 > 80
> ActiveCell.AddComment
> ActiveCell.Comment.Visible = True
> ActiveCell.Comment.Text Text:="Raise Weight to Limit 1"
> ActiveCell.Select
> Case Is < 80
> ActiveCell.AddComment
> ActiveCell.Comment.Visible = True
> ActiveCell.Comment.Text Text:="Minimum Charged"
> ActiveCell.Select
> End Select
> End If
> End Function
>
> As you can see, I don't know enough VBA to program properly yet. Any
> help you can give me would be appreciated.
>
> No greater joy than learning a new trick.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Don
1/12/2004 4:43:05 PM
How about me guys?

Alll I want to know is some VBA code to respond to different ranges of
numbers (I already know how to this with functions such as IF and INDEX
etc. with or without Conditional Formatting) It's the VBA code I want
to learn.

Say for the following ranges,

0-20     Do something like enter the text "Poor"
21-40   Do something like enter the text "Below Average"
41-60   Do something like enter the text "Average"
61-80   Do something like enter the text "Above Average"
81-100 Do something like enter the text "Great"

It has to run in such a way that as soon as one of the above ranges is
calculated by use of functions, the VBA does it stuff and inputs the
text in a specified cell, let's say H20 for arguments sake.

If anyone can tell me how to this, this will be a very big break for
me.

I really would apreciate it.


---
Message posted from http://www.ExcelForum.com/

0
1/16/2004 4:35:36 PM
Hi Andrew,
Look up "select case" in VBA help for one solution.

-- 
John
johnf 202 at hotmail dot com


"Andrew >" <<Andrew.104v5a@excelforum-nospam.com> wrote in message
news:Andrew.104v5a@excelforum-nospam.com...
| How about me guys?
|
| Alll I want to know is some VBA code to respond to different ranges of
| numbers (I already know how to this with functions such as IF and INDEX
| etc. with or without Conditional Formatting) It's the VBA code I want
| to learn.
|
| Say for the following ranges,
|
| 0-20     Do something like enter the text "Poor"
| 21-40   Do something like enter the text "Below Average"
| 41-60   Do something like enter the text "Average"
| 61-80   Do something like enter the text "Above Average"
| 81-100 Do something like enter the text "Great"
|
| It has to run in such a way that as soon as one of the above ranges is
| calculated by use of functions, the VBA does it stuff and inputs the
| text in a specified cell, let's say H20 for arguments sake.
|
| If anyone can tell me how to this, this will be a very big break for
| me.
|
| I really would apreciate it.
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|


0
me5306 (285)
1/16/2004 10:41:42 PM
jaf wrote:
> [B]Hi Andrew,
> Look up "select case" in VBA help for one solution.
> 
> --
> John
> johnf 202 at hotmail dot com
> 
> John,
> 
> You are a lifesaver! I finally got it!
> 
> Ha ha, from now I make my own conditional formatting... :D
> 
> Andrew


---
Message posted from http://www.ExcelForum.com/

0
1/17/2004 11:03:01 AM
Well, I finally bought a beginners VBA programming book last night an
after half an hour of reading, I came up with the following function
(It doesn't calculate anything just yet, it just displays message boxe
to advise me to raise the weight, which was what I was after in th
first place - I can always do the actual calculation in another cell)

Function Airfreight(Weight, Limit1, Limit2, Limit3, Rate1, Rate2
Rate3, Rate4)
If Weight > 0 And Weight < Limit1 And Weight * Rate1 > Limit1 * Rate
Then MsgBox "Raise Weight to 45 kgs"
If Weight >= Limit1 And Weight < Limit2 And Weight * Rate2 > Limit2 
Rate3 Then MsgBox "Raise Weight to 100 kgs"
If Weight >= Limit2 And Weight < Limit3 And Weight * Rate3 > Limit3 
Rate4 Then MsgBox "Raise Weight to 500 kgs"
End Function

I intend to replace the messge boxes with comments later on or perhap
I can use both? More experimentation is required.

If, And & Then was all that I was after, perhaps my explanations wer
too difficult to understand

--
Message posted from http://www.ExcelForum.com

0
2/8/2004 10:13:57 AM
Andrew,

Adapted slightly to return the text (not Msgbox), make it only test until it
matches, and to throw an error if no valid condition is met

Function Airfreight(Weight, Limit1, Limit2, Limit3, Rate1, Rate2, Rate3,
Rate4)
    If Weight > 0 And Weight < Limit1 And Weight * Rate1 > Limit1 * Rate2
Then
        Airfreight "Raise Weight to 45 kgs"
    ElseIf Weight >= Limit1 And Weight < Limit2 And Weight * Rate2 > Limit2
* Rate3 Then
        Airfreight "Raise Weight to 100 kgs"
    ElseIf Weight >= Limit2 And Weight < Limit3 And Weight * Rate3 > Limit3
* Rate4 Then
        Airfreight "Raise Weight to 500 kgs"
    Else
        Airfreight = CVErr(xlErrValue)
    End If
End Function

Maybe this will help in your learning.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andrew >" <<Andrew.11ayt7@excelforum-nospam.com> wrote in message
news:Andrew.11ayt7@excelforum-nospam.com...
> Well, I finally bought a beginners VBA programming book last night and
> after half an hour of reading, I came up with the following function.
> (It doesn't calculate anything just yet, it just displays message boxes
> to advise me to raise the weight, which was what I was after in the
> first place - I can always do the actual calculation in another cell)
>
> Function Airfreight(Weight, Limit1, Limit2, Limit3, Rate1, Rate2,
> Rate3, Rate4)
> If Weight > 0 And Weight < Limit1 And Weight * Rate1 > Limit1 * Rate2
> Then MsgBox "Raise Weight to 45 kgs"
> If Weight >= Limit1 And Weight < Limit2 And Weight * Rate2 > Limit2 *
> Rate3 Then MsgBox "Raise Weight to 100 kgs"
> If Weight >= Limit2 And Weight < Limit3 And Weight * Rate3 > Limit3 *
> Rate4 Then MsgBox "Raise Weight to 500 kgs"
> End Function
>
> I intend to replace the messge boxes with comments later on or perhaps
> I can use both? More experimentation is required.
>
> If, And & Then was all that I was after, perhaps my explanations were
> too difficult to understand?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
2/8/2004 11:10:17 AM
Thanks Bob.

Is "Airfreight = CVErr(xlErrValue)" a VBA equivalent to the ISERRO
function?

Andre

--
Message posted from http://www.ExcelForum.com

0
2/8/2004 12:06:40 PM
Andrew,

No, the opposite in fact. It doesn't test for an error as ISERROR does, it
sets the error. So if there is an invalid value passed in the function, and
none of the 3 criteria that are tested for are met, then the function will
return #VALUE into the calling cell. This makes it more compatible with
built-in Excel functions.

You could also test each argument for individual correctness, and
cross-validate them, but that would be very expensive performance wise in
your function, so I took the single, easy option.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andrew >" <<Andrew.11b412@excelforum-nospam.com> wrote in message
news:Andrew.11b412@excelforum-nospam.com...
> Thanks Bob.
>
> Is "Airfreight = CVErr(xlErrValue)" a VBA equivalent to the ISERROR
> function?
>
> Andrew
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
2/8/2004 12:42:47 PM
Reply:

Similar Artilces:

Calculating Net Worth
I want to project my net worth forward by hand. It seems like this should be a simple calculation, but I'm having troubles making the numbers add up ;) As far as I see it, I should be able to calculate future net worth like this: Future Worth = Net Worth Now + Income - Expenses + Asset Appreciation But I can't /always/ make these numbers work out. when I test the scheme out by "predicting" my current net worth based on real values from a few months ago Here's what I'm doing: 1) I take the net worth (from the net worth over time report) six months ago. 2) Then...

using subtotals with other data to calculate
I need help!! I'm trying to use the subtotals of one worksheet to paste into another to calculate out costs but when I paste them over, I get all of the cells including the subtotals. I only want the subtotals & to be able to match them to another group of data for calculations. Can anyone help me? Doug To copy just the subtotal amounts: 1. Collapse the Subtotals, and select the range of cells that you want to copy. 2. Choose Edit>Go To, click Special 3. Select Visible cells only, click OK 4. Choose Edit> Copy 5. Select the cell where the paste should start 6. Choos...

Calculating in Payroll
We are on 7.5 with Pervasive using Wennsoft for job cost and Windows 2000. There are 3 workstations exactally alike. There's only one that can do any calculations within payroll such as manual checks, building check files and month end and quarterly reports. The other 2 hang up when they go to calculate. Since we are still on Pervasive we don't have the use of MS or Wennsoft for assistance. So far I have: 1. Removed and reinstalled Dynamics and Wennsoft. 2. Copied the .dic files from the one that works. 3. Replaced all files from the C drive (pertaining to Dynamics and WS) of the on...

Calculations in Time Problem
im sure this is obvious but say ive got 2 times A1)1:32.101 A2)1:32.105 how can i do the maths A2-A1 etc , ive formatted everything t mm:ss.000 as ive read that when searching on forum but when i put thi in B1 =A2-A1 i just get #VALUE how do i get it so it gives me the answer 0:00.004 in B1 any help would be much appreciated: -- scottymellot ----------------------------------------------------------------------- scottymelloty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=380 View this thread: http://www.excelforum.com/showthread.php?threadid=48882 Sound...

Calculate the total overlapping time of multiple tasks, excluding non-working times
I am developing a tracking calculator for an overall process. Within the overall process, there are 3 steps which are completed. These steps can be completed independently from one another, but can also (sometimes) overlap. I am trying to determine the total time for the overall process (from beginning to end), without counting the duplicated times where the steps are running parallel to one another, or times when some steps may be completed and are waiting for another to start. I have outlined the basic design of the worksheet below, with times in mm/dd/yyyy hh:mm format. S...

Replace using wildcards??
I have a text field that contains both numbers and text. At the end of some of the data in this field , it has a partial date -- such as 12/24 - 01/23. This date could be different for each record that contains it. I would like this date portion stripped out completely as it is not needed and interferes with queries when grouping. I have been unable to accomplish this using wildcards. Is there another alternative? If you could figure out a defining pattern in the string, such as the date starting at position 25, you could use the Right, Mid, or Left functions to strip o...

How do I use exchange tools to identify who is sending virus from network
What tool can we use to help identify who is sending out virus email through our exchange server? We thought we had all computers protected, but it seems somehow they got in. We are trying to determine where the virus are originating from and not having much luck. Does anyone have any ideas on what tools could help with this? Thanks, Paul Stephenson Paul Stephenson wrote: > What tool can we use to help identify who is sending out virus email > through our exchange server? > > We thought we had all computers protected, but it seems somehow they > got in. We are trying to d...

Optimum Calculation using VBA
I have developed a file to advise me when I should rise the weight of a airfreight shipment to receive the best rates available. (It's still quite rudimentary at this stage) How it works is like this...let's assume anything up to 45 kgs will cost $4.70 per kg, anything equal or above 45 kgs will cost $4.00 per kg. If we consider that 45 kgs X $4.00 costs $180.00, then any shipment above 38 kgs at $4.70 per kg will cost more (39 kgs X $4.70 will cost $183.33, nothing less than a whole kg is valid). Therefore, it is best to raise the shipment weight to 45 kgs to avoid paying undue shi...

When was a DL last used? #2
I am trying to script or develop some sort of way to see which DLs are not being used. I know promodag has something to this effect but I am at a new job that apparently does not have message tracking turned on. Can this be retrieved by date or other means? ...

2-way integration using callouts
Hi, Am doing 2-way integration with CRM and biztalk. Problem is when updating CRM from an external system the integration callout will fire, causing cyclic behaviour. Is there any way to avoid firing a callout when using the SDK? Any recommendations? Nick No easy way to do this. You basically need to add logic to check to see where the update came from or to check the values to see if they've changed. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Fri, 11 Feb 2005 05:29:01 -0800, "Nick Doyle" <nick.doyle...

Auto Calculate
How do I locate the Aucto Calulate function in Excel 2000? I use to be able to highlight cells and have the sum appear at the bottom. Now it has "min=". Linda, Right click in that min and select sum in the popup menu. -- Regards, Auk Ales * Please reply to this newsgroup only * * I will not react on unsolicited e-mails * "Linda A" <LAdams@shamrockcompanies.net> wrote in message news:C43E04EF-C988-41B7-A38D-2464C6658E61@microsoft.com... > How do I locate the Aucto Calulate function in Excel 2000? I use to be able to highlight cells and have the sum appear ...

How is GP Calculating the Suggested
I have an issue where the Order Point Quantity is set to a number e.g. 200. When the PO is generated using PO Generator it doesn't create a Suggested Purchase Order. When the Order Point Quantity is increased to 400 it generates a suggested Purchase Order for 98. Where is the PO Generator basing its calculation for the suggested quantities. In the above Scenario, there are no stock in the Site/Warehouse and should create 200 as suggested Purchase Order. esage, PO Generator is seeing some quantity somewhere. Do you have other Sites where this item is stocked that PO Gen may be seein...

Excel calculate the z factor automatically
How do Excel calculate the z factor automatically EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com ...

Using POS in Grocery Store
This questions. 1. I want to use the system in Grocery store. I have to set up a servers and 10 registers. I will accept FoodStamp and some other types of cards. What is the way to set up system accept Food Stamp. I mean I have set up all configurations for it, but I just want to know if I slide a FoodStamp card , how to make system charge from it. I need to set up FoodStamp merchant account separately or just purchase merchant account with food stamp support. I don't know ... can anybody help me with this ? 2. I will need 10 scale-scanners. is there any roght scheme to set up all t...

Perform a calculation????
I'm curious if anybody knows how I can do a calculation on two fields in CRM. In the Opportunity form I created a Gross Profit custom field and what I want to do is calculate Gross Margin by taking the Est. Revenue minus the estimated Gross Profit then calculate the Gross Margin and populate a field with the result. Any help out there? Only way to do this is when you create some event code which is attached to picklist fields. An eaxmple would be a picklist where you have shipping methods such as UPS, Fedex etc. When you choose the method the event code updates the shipping cost etc &q...

Calculate fees based on the percentage given
Based on the example and table in the attachment, I want to calculat Total Cost based on a specific percentage. At first, I need to calculate based on Class 2 minus Class1. The Total Cost is calculated after a fees is entered. I want the steps of calculation (shaded in blue in my worksheet) to be displayed in the worksheet too. How can i do that? Any and all help are appreciated! Thanks, p/s Please refer to the attachment +---------------------------------------------------------------- | Attachment filename: book1.xls |Download attachmen...

IF calculations
number in "A1", number in"A2". I want to calculate difference and put the difference in "A3" except if "A2" is input as "0", I want to put "0" in "A3". Thanks -- jpmiii =IF(A2=0,0,A2-A1) Or maybe =IF(A2=0,0,A1-A2) Gord Dibben MS Excel MVP On Fri, 11 Jul 2008 16:46:06 -0700, jpmiii <jpmiii@discussions.microsoft.com> wrote: >number in "A1", number in"A2". I want to calculate difference and put the >difference in "A3" except if "A2" is input as "0", I...

Using calculated filenames
Ok, Here's another thing I'm having trouble getting to work. I would like to concatenate a file and cell name to use to retrieve a value. I have a master sheet that contains a filename value in a cell. I would then use the filename value contained in that cell to call a value from another sheet. Here is an example of what I mean: File_Names.xls!FILE_SALES_JUNE_2006 ['FILE_SALES_JUNE_2006' is concatenated, cell contains value...] ---- Sales_June_2006.xls ---- I would then use that value to call a concatenated cell value from another sheet Sales_June_2006.xls!S_1001...

Use with Work and Home Accounts
When I click an IM link in WLMD I get the message "Windows Live Messenger is currently not signed-in with the same user acocunt...". I am using my work Win Live ID in Win Live Messenger and Home Win Live ID with WLMD. I want to stay logged in Messenger with thw Work Live ID. I have Linked my Win Live IDs but it still asks me to log out. Is there any way I can stay logged in to Messenger with by Work Live ID and access Messenger with WLMD using Home Live ID? If accessing the IM link from a contact in WLM, then no. You can open WLM with one ID and open WLMsg with anot...

calculation sundays between two dates
cell A1 has start date (eg) 12/08/05 cell A2 has end date (eg) 25/08/05 and cell A3 has the formular that displays a the number of sundays occured (in this example the result is 2) jeff thinkin Wrote: > cell A1 has start date (eg) 12/08/05 > cell A2 has end date (eg) 25/08/05 > > and cell A3 has the formular that displays a the number of sundays > occured > (in this example the result is 2) Hi jeff Try this > =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A14&":"&B14)))=1)) Where the red 1 indicates a Sunday, 2 would be Monday etc -- Paul Sheppard --...

Once only calculation
Hi - I'd be very grateful if someone can help me with a bit of macro cod that might achieve this. Really having trouble as my programming skil is diabolical!! I want to perform a "once only" calculation on rows of cells. When reference cell changes from blank to a value it will trigger calculation in another cell. When the reference cell change subsequenlty it will not affect the value in the calculated cell. e.g. Cell - Formula - Value -------------------- Step 1 A1 blank B1 =A1 value blank Step 2 A1 value 10 B1 =A1 value 10 Step3 A1 value 20 B1 =A1 val...

accessing the internet via optimum
I currently use optimum for access to the internet. How do i figure out what the "pop server" is? i have no idea of how to set up the server information needed to send and receive emails. Optimum may be your ISP but are they also your preferred mail service provider? If so, see here: http://optimum.custhelp.com/cgi-bin/optimum.cfg/php/enduser/std_adp.php?p= _faqid=3D2397&p_sid=3D_1HXIKNj&p_lva=3D27 --=20 Gary VanderMolen, Microsoft MVP (Mail) http://mvp.support.microsoft.com/default.aspx/profile/vandermolen "Lavada" <Lavada@discussions.microso...

calculate percentile for indexed numbers. Thanks #2
Hi, I have a large series of numbers saved in one column [34,5,21,98,34,3,432,55,27,90,1125,......]. These data are indexed, e.g. by [1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,......N] which are in another column. Basically I am calculating the 25% percentiles for numbers indexed "1", "2", "3",...., "N" respectivelly. There are a lot of numbers with a big uplimit of index "N" so that selecting numbers of each index manually is not easy. In another word, if N = 9999, then I need to calculate N 25% percentiles with one for each indexed number cla...

Subtotal not calculating fully
I have a simple function to subtotal a long column. The function is =SUBTOTAL(9,P7:P850) some of the cells in the column have values but don't add to th total. Any help would be appreciated -- Message posted from http://www.ExcelForum.com Some of the values are most likely entered as Text. Coerce them to numbers by selecting an empty cell, copying it, selecting your numbers and choosing Edit/Paste Special, selecting the Values and Add radio buttons. In article <marvontherim.17zcdw@excelforum-nospam.com>, marvontherim <<marvontherim.17zcdw@excelforum-nospam.com>>...

CheckBox Calculation
I have a check box on a table. When I click it I want it to perform a calculation plugging in a new service date. I want to basically auto calculate after a task is completed and plug that value into the table. can you please help me in a good direction or approach to conquer this task. -Calvin thank you Calvin, First, if you're editing or entering data directly into your table... that should be avoided. That's what forms are for. A table has no events, like a check box AfterUpdate event, on which to trigger your code. Setting a checkbox to True on a form, would f...