## Need Help Writing Formula To Calculate Construction Performance & Payment Bond |

## Need Help Writing Formula To Calculate Construction Performance & Payment Bond - Excel |
View Answers |

I need to write a formula that will calculate the cost of a construction performance and payment bond. The bond is calculated as a percentage of the contract amount. Let's just say the contract amount is in cell A1 on the spreadsheet. The cost of the bond will be 2.5% of the first $100,000 of contract, 1.5% on the next $500,000 of the contract, and then 1.0% on the next 2,000,000 and so on. I have put the table below that references every percentage we would use. Remember that the formula needs to work even if the contract amount is as small as $1 or as large as millions of dollars. I am terrible at these types of formulas and could use some help. Thank you for your assistance!

BOND RATE BOND AMOUNT

2.50% Up to 100,000.00

1.50% On the Next 400,000.00

1.00% On the Next 2,000,000.00

0.75% On the Next 2,500,000.00

0.70% On the Next 2,500,000.00

0.65% Amount Over 7,500,000.00

## Free Excel Courses

**Getting Started in Excel (Course)**

**Introduction to Programming Macros in Excel (Course)**

**Reference Other Excel Files with Formulas and Functions**

## Similar Excel Tutorials

Stop Formula Calculation in Excel - Increase Worksheet Performance

I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...

I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...

FV Function - Get the Future Value in Excel

The Future Value function (FV) in Excel will return the future value of an investment based on a particular intere ...

The Future Value function (FV) in Excel will return the future value of an investment based on a particular intere ...

PMT Function - Get the Payment Due for a Loan in Excel

How to calculate the payment amount for a loan or similar financial instrument that has a series of constant paymen ...

How to calculate the payment amount for a loan or similar financial instrument that has a series of constant paymen ...

NPER Function - Calculate Number of Periods Needed for a Set of Payments in Excel

How to calculate the number of periods required for an investment in order to get the desired return. The number o ...

How to calculate the number of periods required for an investment in order to get the desired return. The number o ...

## Subscribe for Weekly Tutorials

### Helpful tutorials delivered to your email!

## Helpful Excel Macros

Convert Numeric Dollar Values into Text in Excel - UDF

- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra

- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra

Hide Formulas in a Worksheet and Prevent Deletion

- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li

- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li

Change Formulas to Absolute or Relative References

- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o

- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o

Close a Workbook after a Time Limit is Reached

- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai

- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai

Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro

- This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th

- This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th

## Similar Topics

A previous poster explained what I also need; there were no responses,

so I thought I would try again.

Does anyone know of an Excel template--or a small stand-alone

program--that will calculate the interest/principal breakdown when

payments are varied in amount and frequency? Free or low-cost, please.

I need one that will work on my Mac/Office 2004. I will need to print

out periodic reports.

Here is the way the previous poster described it:

"Excel template: Loan Amortization for random/irregular payments,

figures days

between payment dates.

I have a loan with a variable beginning balance and irregular payments

with

annual large payment. (based on collections)

Would like to enter payment and date.

then Excel would figure days since last payment, interest amount,

principal

amount, Ending Principal balance.

And total interest paid, total pricipal paid

If I change the starting principal, excel would recalculate all

entries."

Thanks very much.

so I thought I would try again.

Does anyone know of an Excel template--or a small stand-alone

program--that will calculate the interest/principal breakdown when

payments are varied in amount and frequency? Free or low-cost, please.

I need one that will work on my Mac/Office 2004. I will need to print

out periodic reports.

Here is the way the previous poster described it:

"Excel template: Loan Amortization for random/irregular payments,

figures days

between payment dates.

I have a loan with a variable beginning balance and irregular payments

with

annual large payment. (based on collections)

Would like to enter payment and date.

then Excel would figure days since last payment, interest amount,

principal

amount, Ending Principal balance.

And total interest paid, total pricipal paid

If I change the starting principal, excel would recalculate all

entries."

Thanks very much.

I am needing a formula that would cause a currency amount from a calculation to round up or down to the nearest 100.00 mark. The current formula is ie: =E10*F10 (e10 being an amount of money and f10 being a percentage fo it.) I am needing the resulting answer to round up or down to the nearest 100. So if the answer is below the 50 mark it would round down and above it would round up. If that is not possible then just rounding down would be acceptable.

How do i do this??

Thanks

How do i do this??

Thanks

Hi there

I am trying to calculate our On Time Delivery. I want this as a simple percentage of jobs. I have got this running in the following way:

Column M - Estimated Delivery dates

Column N - Actual Delivery dates

Column O - =IF(SUM(M2-N2)>0,1,0)

Then I have calculated On Time Delivery as: =SUM((SUM(O2:O252))/(COUNTA(N2:N252)))

This seems to work fine. My problem is, if we enter a date in Estimated without a corresponding Actual date, the formula for Column O fills out anyway and improves our On Time Delivery Percentage. How can I set this up so that the formula doesn't calculate if there is no data in Column N?

Any help much appreciated

Thanks,

James

I am trying to calculate our On Time Delivery. I want this as a simple percentage of jobs. I have got this running in the following way:

Column M - Estimated Delivery dates

Column N - Actual Delivery dates

Column O - =IF(SUM(M2-N2)>0,1,0)

Then I have calculated On Time Delivery as: =SUM((SUM(O2:O252))/(COUNTA(N2:N252)))

This seems to work fine. My problem is, if we enter a date in Estimated without a corresponding Actual date, the formula for Column O fills out anyway and improves our On Time Delivery Percentage. How can I set this up so that the formula doesn't calculate if there is no data in Column N?

Any help much appreciated

Thanks,

James

Hi,

Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too.

I need to calculate only the Yes.

I have tried using the filters in the Pivot Table field list and they are not working.

So now i'm trying to add a COUNTIF formula in the Calculated Fields section.

The formula I'm using is =Countif('Time limit extension'="yes") and i get the answer "too few."

What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me.

Please HELP!!!

Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too.

I need to calculate only the Yes.

I have tried using the filters in the Pivot Table field list and they are not working.

So now i'm trying to add a COUNTIF formula in the Calculated Fields section.

The formula I'm using is =Countif('Time limit extension'="yes") and i get the answer "too few."

What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me.

Please HELP!!!

Hello Everybody,

I am hoping for some assistance. Here is what I have....

I am trying to get counts of certain values.

This is in the same workbook but different sheets.

On sheet 1 is where my formula lies. In a cell, I want it to look at sheet 2 column B and look for instances of "A", then I want it to look in the same row it found "A" and look at the value in column "N" and evaluate if there is an amount. If there isn't skip it and don't count it, if there is a value count it if it falls within a given range.

An example of the formula is below...what am I missing?

=COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=0:500))

=COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=501:1000))

Thank you in advance for your assistance.

I am hoping for some assistance. Here is what I have....

I am trying to get counts of certain values.

This is in the same workbook but different sheets.

On sheet 1 is where my formula lies. In a cell, I want it to look at sheet 2 column B and look for instances of "A", then I want it to look in the same row it found "A" and look at the value in column "N" and evaluate if there is an amount. If there isn't skip it and don't count it, if there is a value count it if it falls within a given range.

An example of the formula is below...what am I missing?

=COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=0:500))

=COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=501:1000))

Thank you in advance for your assistance.

Sale = A1

Cost = B1

Margin = C1

I know to calculate Margin its' C1=1-(B1/A1) and to calculate Sale its, A1=B1/(1-C1), and I can do that in separate columns. But...... I'm tasked with trying to find a way so that I can do it in three columns. As in if I change cost then margin recalculates or if I change margin then sale recalculates. I'm thinking that this is going to cause a circular error but my boss says there's got to be a way to do it, figure it out. Any suggestions?

Alex

Cost = B1

Margin = C1

I know to calculate Margin its' C1=1-(B1/A1) and to calculate Sale its, A1=B1/(1-C1), and I can do that in separate columns. But...... I'm tasked with trying to find a way so that I can do it in three columns. As in if I change cost then margin recalculates or if I change margin then sale recalculates. I'm thinking that this is going to cause a circular error but my boss says there's got to be a way to do it, figure it out. Any suggestions?

Alex

Hello all,

New to the forums.

I'm in the process of creating a new report and I'm stuck.

My report consists of a set target in a field, a score and percentage of target.

e.g Target is 400

score is 450.

Percentage is 1.125 of target. (=score/target)

Now what I would like to do is create a formula that does not go above 100% , but instead work in reverse if you go over the target.

E.g. Target 400 .. Score was 450 ... percentage of target is 87.5%

Thanks in advanced.

New to the forums.

I'm in the process of creating a new report and I'm stuck.

My report consists of a set target in a field, a score and percentage of target.

e.g Target is 400

score is 450.

Percentage is 1.125 of target. (=score/target)

Now what I would like to do is create a formula that does not go above 100% , but instead work in reverse if you go over the target.

E.g. Target 400 .. Score was 450 ... percentage of target is 87.5%

Thanks in advanced.

I have a spreadsheet with simple (addition/subtraction) formulas. The file is quite large and the formulas are too. All of a sudden, the formulas stopped working except when I double click in the cell containing the actual formula. For example, if I enter "2" in each cell, A2 and B2, cell B3 should reflect "4" because there is a formula in cell B3 which totals cells A2 and B2. Only by double clicking on cell B3 will the program actually calculate. I'm totally perplexed and so is my IT contact. Anyone?

Entering time values in custom format [h]:mm:ss

Cells accept hours over 23,

Adding cells in column returns correct total time.

Have not found a way to multiply these cells by a $ hourly rate.

So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced

cell - then use these values to calculate total payment for $rate per hour.

However, the HOUR(cell ref) formula returns the hours in excess of 24 when

the cell contains an hour value in excess of 23 (ie 27 hours returns 3).

Cells accept hours over 23,

Adding cells in column returns correct total time.

Have not found a way to multiply these cells by a $ hourly rate.

So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced

cell - then use these values to calculate total payment for $rate per hour.

However, the HOUR(cell ref) formula returns the hours in excess of 24 when

the cell contains an hour value in excess of 23 (ie 27 hours returns 3).

Hello gurus!

Here's my question...I have a list of numbers and I want to know how many combinations (and what they are) of adding the numbers will equal an amount.

e.g.

List of Numbers

1

2

3

4

5

6

7

8

9

10

Amount to be reached = 12

These are some of the possible combinations to reach 12 :

2+10

3+9

4+8

5+7

3+4+5

6+3+2+1

Is there a function in Excel that will do this for me? I want to know which numbers (i.e. cells) can be added to reach 12. AND, if possible, colorcode the cells added for each combination.

Good luck and thanks!!

Here's my question...I have a list of numbers and I want to know how many combinations (and what they are) of adding the numbers will equal an amount.

e.g.

List of Numbers

1

2

3

4

5

6

7

8

9

10

Amount to be reached = 12

These are some of the possible combinations to reach 12 :

2+10

3+9

4+8

5+7

3+4+5

6+3+2+1

Is there a function in Excel that will do this for me? I want to know which numbers (i.e. cells) can be added to reach 12. AND, if possible, colorcode the cells added for each combination.

Good luck and thanks!!

I have a spreedsheet that shows some percentages example( 9.98% and 15.87%) I am needing to find a way to hide the percentage sign so that they just show as 9.98 and 15.87. The problem I am having is that are been worked out as a percentage, so changing the format of the cell does not work.

Can anyone help?

Thanks

Chris

Can anyone help?

Thanks

Chris

I have a spreadsheet where I need to calculate the increase in % one year over the other in column D for Eg if Profits are 95415 in Jan 2008 (Column B) and 80215 in Jan 2008 (Column C), then the % Increase will be (95415-80215)/80215 *100

I can calculate the formula for this , but the problem comes where for eg there is a loss in Jan 2008 for -95415 and there was a profit in Jan 2007 for 80215. How do I set up formula to take negatives & positives into account in calculating the increase or decrease/

Your assistance or anybody's on the forum will be greatly appreciated

Regards

Howard

http://www.mrexcel.com/forum/showthr...ghlight=howardneed

I can calculate the formula for this , but the problem comes where for eg there is a loss in Jan 2008 for -95415 and there was a profit in Jan 2007 for 80215. How do I set up formula to take negatives & positives into account in calculating the increase or decrease/

Your assistance or anybody's on the forum will be greatly appreciated

Regards

Howard

http://www.mrexcel.com/forum/showthr...ghlight=howardneed

Hey everyone,

I'm really hoping someone can help me with this...

I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....

Can someone please offer a suggestion for how to do this? I would really appreciate it.

Also, would it be possible to link data from other sheets in the workbook into one single chart?

I'm really hoping someone can help me with this...

I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....

Can someone please offer a suggestion for how to do this? I would really appreciate it.

Also, would it be possible to link data from other sheets in the workbook into one single chart?

Also, I cannot move the Pivot Table from the new Worksheet it is created in, to my existing Worksheet.

I am running Excel 2011 for Mac.

I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. The Pivot Table data is fine, but I want it to display alongside the source data.

I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Also, I checked in Edit menu and found that the Pivot Table appears to have not even been created/moved because there is no option to undo it.

Appreciate any help, let me know if you have any further questions.

Hunter

I am running Excel 2011 for Mac.

I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. The Pivot Table data is fine, but I want it to display alongside the source data.

I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Also, I checked in Edit menu and found that the Pivot Table appears to have not even been created/moved because there is no option to undo it.

Appreciate any help, let me know if you have any further questions.

Hunter

I have an excel worksheet that adds two other worksheets in a data

triangle. I copied it to create a new data set and used find &

replace to change the worksheet references to the new ones.

The cells still contain the result of the old formula referring to the

previous worksheets. The only way I can get the formula to return the

correct result is to edit (F2) each cell and press enter. Calc now

(F9) does nothing.

I've seen this before, but this time, I need to calculate many

thousands of cells and don't have time for this workaround.

Any ideas?

Thanks.

Don S

triangle. I copied it to create a new data set and used find &

replace to change the worksheet references to the new ones.

The cells still contain the result of the old formula referring to the

previous worksheets. The only way I can get the formula to return the

correct result is to edit (F2) each cell and press enter. Calc now

(F9) does nothing.

I've seen this before, but this time, I need to calculate many

thousands of cells and don't have time for this workaround.

Any ideas?

Thanks.

Don S

I am trying to correct a formula for a productivity worksheet in excel. The idea is that we would have the ability to input the number of hours worked each day and the number of direct hours (working with clients) per day. Then take the daily percentages and add them then devide them to determine the final productivity percentage for the week. Additionally not everyone works a full week so I would need it to only divide the percentages of the days that were completed. Any suggestions? or does anybody know a formula that would accomplish this already? I am good with basic excel and was able to identify a flaw in the current productivity sheet.

Hi

I have a table with various percentages, and I would like a formula to have an average of these. Please see attached file to get more of an idea.

Thanks

G

I have a table with various percentages, and I would like a formula to have an average of these. Please see attached file to get more of an idea.

Thanks

G

Hi all,

I have several accumulated values from other spreadsheets on my spreadsheet and I'm combining and organizing them on mine. However, the cell alignment isn't working properly as you can see in the picture below.

Now, I can certainly retype these values and align them and they'll align all nice and neat; however, I have a huge Column and retyping that column would take an inordinate amount of time.

I searched earlier on cell alignment and couldn't find anything. Any help is appreciated.

Sincerely,

Bill

I have several accumulated values from other spreadsheets on my spreadsheet and I'm combining and organizing them on mine. However, the cell alignment isn't working properly as you can see in the picture below.

Now, I can certainly retype these values and align them and they'll align all nice and neat; however, I have a huge Column and retyping that column would take an inordinate amount of time.

I searched earlier on cell alignment and couldn't find anything. Any help is appreciated.

Sincerely,

Bill

First time in this forum. Hi all. I have a challenging question, is there a way either by VBA or manually (preferably both, if possible) to actually unite the X amount of numbers that are in a cell given the contents is alphanumeric? I'll give you the following examples to see if you can understand what I' referring to?

DATA output should be

asd67,h876 --------> 67876

2,3,ujdj5&34 -------> 23534

909k86m34 --------> 9098634

Hope this makes sense?

DATA output should be

asd67,h876 --------> 67876

2,3,ujdj5&34 -------> 23534

909k86m34 --------> 9098634

Hope this makes sense?

I need a formula to calculate Turn Around Time. Find below the sample data and details for your reference.

Received Date & Time: 10/29/2010 3:27:00 PM

Completed Date & Time: 11/1/2010 3:57:32 AM

Business Hours: 08:00 AM to 05:00 PM

Non Business hours: 05:00 PM to 08:00 AM

Weekdays : Monday to Friday

Weekends: Saturday & Sunday

If I calculate TAT for this time period it should show me 1 hour 33 minutes, why because business hours are closing by 5:00 PM on 10/29 so it should not calculate duration after 5:00 PM to until 08:00 AM on next day, however 10/29 is Friday so formula should calculate the duration from 10/29 Friday 3:37 PM to 10/29 5:00 PM and again it should calculate from 8:00 AM on 11/01 Monday and formula should skip Saturday & Sunday.

In this scenario I have completed the task before 8:00 AM on 11/01 so it should show duration of 1hour 33 minutes.

Please let me know if you need any more details

Received Date & Time: 10/29/2010 3:27:00 PM

Completed Date & Time: 11/1/2010 3:57:32 AM

Business Hours: 08:00 AM to 05:00 PM

Non Business hours: 05:00 PM to 08:00 AM

Weekdays : Monday to Friday

Weekends: Saturday & Sunday

If I calculate TAT for this time period it should show me 1 hour 33 minutes, why because business hours are closing by 5:00 PM on 10/29 so it should not calculate duration after 5:00 PM to until 08:00 AM on next day, however 10/29 is Friday so formula should calculate the duration from 10/29 Friday 3:37 PM to 10/29 5:00 PM and again it should calculate from 8:00 AM on 11/01 Monday and formula should skip Saturday & Sunday.

In this scenario I have completed the task before 8:00 AM on 11/01 so it should show duration of 1hour 33 minutes.

Please let me know if you need any more details

Great tip! But one thing: On my machine (Excel 2008 Mac), the values in the formula cell are not correctly calculated simply by dragging. Instead, the values are equal to the cell above (the first entry formula value). To get the correct value, I need to click in the formula bar and then hit enter. (I only discovered this after an hour of tinkering, figuring I had botched the formula!)

Are there any ways around this so that it updates upon dragging the formula?

Are there any ways around this so that it updates upon dragging the formula?

I have a spreadsheet that when I copy the formula, it copies correctly

(changes the cells it should subtract), however, the result stays the same.

It matches the formula I copied it from, even though the cells to calculate

are now different. I even did a paste special and said only formula, but

still, same result. If I actually type in the formula, it works fine, but I

have a lot I need to copy and paste. Is there a way I can fix this?

Thanks,

(changes the cells it should subtract), however, the result stays the same.

It matches the formula I copied it from, even though the cells to calculate

are now different. I even did a paste special and said only formula, but

still, same result. If I actually type in the formula, it works fine, but I

have a lot I need to copy and paste. Is there a way I can fix this?

Thanks,

I am trying to do a very simple copy and paste of a simple formula in Excel 2007 and nothing seems to be working. Excel will not let me copy and paste a formula and will only paste the value into to workbook. For instance, if I make a very simple spreadsheet such as:

A1 type in 10, A2 type in =A1 (calculated A2 to be 10)

B1 type in 5

And then click the copy on B1, and then click paste special on cell B2, the only options it gives me are text and unicode text and so no matter how I paste, cell b2 will always be populated with a value of 5. I cannot imagine a simpler copy and paste and no matter what I do I can't make this work.

All cells are formatted as general. It seems that all copy and pasting of formulas in my excel has been disabled. If I open any spreadsheet on my computer, I can't copy and paste formulas, but do the exact same thing on the exact same spreadsheet on any other computer and it works no problem.

Any help?

A1 type in 10, A2 type in =A1 (calculated A2 to be 10)

B1 type in 5

And then click the copy on B1, and then click paste special on cell B2, the only options it gives me are text and unicode text and so no matter how I paste, cell b2 will always be populated with a value of 5. I cannot imagine a simpler copy and paste and no matter what I do I can't make this work.

All cells are formatted as general. It seems that all copy and pasting of formulas in my excel has been disabled. If I open any spreadsheet on my computer, I can't copy and paste formulas, but do the exact same thing on the exact same spreadsheet on any other computer and it works no problem.

Any help?

I need information on how to enter the proper syntax (formula) so that excel

can calculate overtime hours. In california over 8 hrs in one day is

overtime. The 8 would be considered regular hours and anything over is OT.

can calculate overtime hours. In california over 8 hrs in one day is

overtime. The 8 would be considered regular hours and anything over is OT.

This formula is supposed to calculate difference between today and date hired to give years and months of service. Works fine for all except if the person was hired in year 2000 then this formula shows 110 y. Any one know how to fix this? or have a different formula that works.

=DATEDIF(E21,TODAY(),"y")&" y "&DATEDIF(E21,TODAY(),"ym")&" m"

Thanks

Rick

=DATEDIF(E21,TODAY(),"y")&" y "&DATEDIF(E21,TODAY(),"ym")&" m"

Thanks

Rick