Free Microsoft Excel 2013 Quick Reference

# Formula Lookup? (Making a Freight Calculator)

Hello All

Im fairly new to big formulations in excel

im trying to make a Freight Calculator

Where you will enter the Region(B1:F1) to a cell B11 and weight(A2:A7) into Cell B12 and it will return the rate(B2:F7) to Cell B13 multiplied by the weight

i work out how to do it with entering the Exact weight listed but if the weight is different it would return an error

so it need to find the rate between a certain range? im not sure if this makes sense

( See attached )

## Related Results

### Building a Freight Calculator Project

I need assistance in finishing my project to build a freight calculator for my company. I have a week to finish and I am quite slow in formula. Please help.

The database I built is in “RateData” tab, and i need to simplify into each table for the full load rate and half load rate. I am confused to get the lookup formula work. I need to match the carrier, from, and to with “FTL” or “LTL” and then looking up for the rate value in the total (for Full Load Rate) or single space rate (for half load rate).

Thank you so much for helping me out.

Cheers
Desperato

### Can you use a formula to make a truly "blank" cell

I have a setup that (simplified slightly) looks like:

A1: 20
B1: =if(A1Options
checked, so that blank values are not plotted as zeros.) However, it is
graphing my "not quite blank" cells, generated by the above formula, as
zeros. It does skip truly blank values (if I delete the formula altogether),
but doesn't when I try to force blank values using a formula like this.

Any ideas how to get a formula to make a cell think it is blank?

Thank you,

Heidi

### Can you use a formula to make a truly "blank" cell

I have a setup that (simplified slightly) looks like:

A1: 20
B1: =if(A1<50,"",A1)
C1: =isblank(B1)

C1: produces "false". Why? Isn't "" supposed to put "nothing" into a cell?

The point of all this: I want the cell to be truly blank, so that it doesn't
graph as a "zero" in a chart. (Yes, I have the option under Tools>Options
checked, so that blank values are not plotted as zeros.) However, it is
graphing my "not quite blank" cells, generated by the above formula, as
zeros. It does skip truly blank values (if I delete the formula altogether),
but doesn't when I try to force blank values using a formula like this.

Any ideas how to get a formula to make a cell think it is blank?

Thank you,

Heidi

### Formulas: Lookup for a text field

I'm trying to use a lookup function to coincide with a sumif function. The data are in columns and I would like to have a formula lookup the text value in the column based on the input field. I've tried lookup, hlookup, vlookup and dget but none have worked for me.

### Freight Calculator

I'm building a freight calculator and am considering some professional consulting options, but before I do that I wanted to see if I could overcome this one problem. If I can, I think I might be able to complete the calculator myself.

Here's my conundrum:

A potential customer enters "80802" for zip code and "Solomon" for store.

StoreLocation_________ City_______ State_____ Zip______ Distance
Solomon__________ Arapahoe _______CO ____ 80802_____ 270
Garden City_______ Arapahoe_______ CO _____80802_____ 143

The formula (or series of formulas) I'm looking for would then refer to the following hidden sheet and return Arapahoe, CO and a distance of 270 miles from Solomon.

Any insight would be very appreciated. Thanks.

### One formula to make a multiplication table work

I am looking for the one formula in the upper left hand box that can be
copied thru the worksheet to make a multiplacation table work.

### Need help making a small calculator

Hello everyone. Can someone please help me make a very small calculator. It's a little hard to describe, but I need to look like the following
Let's say the individual got here on 1 Jan

Phase 1 is for days 1-14 last for 14 days
Phase 2 is for Day 15-35 last for 21 days
Phase 3 is day 36+

This is what I need it to look like: All the phase information will compute automatically after inputting the date arrived. In addition I would also like to be able to change one of the phase dates and the remainder of the phases compensate according to the newly entered date.

Date Arrived = 01 Jan 09
Phase 1 = 01jan09
Phase 2 = 04Feb09
Phase 3 = 05 Feb09

### Is there a formula that changes a specific cell to whatever I click on in that sheet.

I am trying to find a formula that makes a particular cell whatever I click on in the sheet. eg A1 would always equal whatever cell I click on in the sheet and change as I click different cells. Does this exist? This is not a joke post. I want to quickly calculate shipping charges. I have all the data in a sheet. So when I for example click on 10 pounds in zone 1 I go across and see \$12.67, then I input that number in A1 and the rest is formulated to do the rest. I wondered if there was a formula that would change A1 to 12.67 automatically instead of me having to input it manually. Hope this clarifies and thanks for the tips. Sorry for the messy start.

### Making a master calender linking other worksheets

I need help with some formulas to make a master calendar/schedule linking all my projects to this schedule. I'm in construction and here is how my system is set up.... I do an estimate on a workbook, if that estimate turns into a job, I drop that file into another folder where my master accounting workbook is. I want to add a master schedule to this folder where all my jobs are tallied on a calendar. In my estimate workbooks, i have a cell for job duration and job start date. I want to like these to the master calendar to automatically get input to the right date slot on the calendar and for the duration that is specified. Is this even possible?
steve

### Making a worksheet sort information, and file it into a workbook

As part of my record keeping I keep two separate logs. One is simply a worksheet into which I list all my receipts for the week, listing the date, value, vendor, job name, and how I paid, credit, cash, debit... Next I have a workbook, (Titled; Jobs Summary), with all my jobs in it, each job has its own sheet, (where I log all this same information again), and all these sheets report back to one main sheet, (Main Summary). I currently have to re-enter all the information, as well as sort through the receipts and open different workbook pages, (as each page is a different job). I would like to set up the weekly worksheet so that as I type in the receipt information, Excel will take that row of information and automatically record it into the workbook, on the correct sheet, according to the job name, (each sheet in the workbook is named according to the job it represents). I could really use some help to get this going, if you have any questions, or I am not being clear please ask, I will be happy to help you help me!

Please note that while I am familiar enough with Excel to write a few formulas and make a work book, I am NOT so familiar as to be able to write macros and the such like. I generally search thru a 'Dummies' book I have to find solutions to my questions.

Thanks
Jack

### How to make a formula to calculate the right answered questions for the Questionnaire

I am analyzing my Questionnaire where i want to calculate the results using Excel.

Supposedly there are 5 Multiple Choice Questions asked to the different set of peoples.

Sno Q1 Q2 Q3 Q4 Q5 TOTAL
1 A B C D E ?
2 A C B D E ?
3 B D D E A ?
4 A C A D A ?
5 A B C D A ?

I want to calculate that how out 5 questions, how many correct answers has been given by person 1?
If the answer to question 1 =a, q2=a, q3=c, q4=b,q5=d then how many answers in total has the person 1 has given correct...??

### Can I make a simple fill-in calculator for webpage using Excel?

I want to make a fill-in calculator for a website using frontpage. It
requires andaverage of 5 numbers tht need to be inputted along with another
number that is used as a multiplier. Average of five numbers times multiplier
(number between 5 and 30 to 2 decimals) times 2% then divided by 12. I can
build the spreadsheet and formula my stumble is getting cells locked down and
transferred to Front page.Using Excel XP and Frontpage 2000

### Making a portion of reference to a worksheet variable in a formula

How do I make a portion of a worksheet reference variable.

For example I am linking to a file that has multiple worksheets and I am
getting the same data from each sheet. I want to be able to in my formula
link to a column that has the a portion of the worksheet name in it. For
example, column A would list numbers 1 thru 10 and my worksheet name are H1,
H2, H3 .... H10.

Depending on what is in column A determines what worksheet the formula pulls
from.

I am using a two way table lookup so I am using index function & Match like
the following and I want to make the 10 after the H variable (dependent on
what is in column A) . How do I do that

=INDEX('[filename.xls]H10'!\$c\$1:\$g6,MATCH(j5,'[filename.xls]H10'!\$c\$1:\$c\$6,0),MATCH(j6,'[filename.xls]H10'!\$c\$5:\$g\$5,0))

--
Celia

### Make a Fresnel Zone Calculator

"SlimPickins" > wrote...
>I wantt to make a calculator with excel. I know some excel basics, but
>I need help with the formula in the link below.
>
>http://www.softwright.com/faq/engine...Clearance.html

Presumably you'd be entering N, lambda, D_1 and D_2, and want F_N. If so,
calculate F_N as

=SQRT(N*lambda*HARMEAN(D_1,D_2))

>I would also like downtilt and azimuth calculators from the link
>below.
>
>http://www.wisp-router.com/calculators/fresnel.php

I'd need to edit this site's source to find the formulas in the php code.
You wouldn't happen to have a more explicit reference, would you?

### Copying formulas without changing previous results/calculations

I am a teacher who gives students points for attendance. If they are gone,
they have to use sick leave, bereavement leave, etc. to avoid having to make
up an article summary. Once they have used all of their leave, they must do
an article summary to get their attendance points for a missed class period.

I want to set up a spreadsheet to figure their attendance points
automatically. I have used the following two formulas:

=IF(COUNTIF(\$G\$2:\$FZ\$2,"P")=0,1,IF(2-COUNTIF(\$G\$2:\$FZ\$2,"P")>0,2-COUNTIF(\$G\$2:\$FZ\$2,"P"),0))

=IF(AND(\$B\$2=0,G2="P"),0,IF(AND(\$C\$2=0,G2="S"),0,I F(AND(\$D\$2=0,G2="F"),0,IF(AND(\$E\$2=0,G2="A"),0,IF( AND(\$F\$2=0,G2="E"),0,LOOKUP(G2,{"P","S","F","A","E ","T","B","BB","BBB","TB","TBB",""},{3,3,3,3,3,2,2 ,1,0,1,0,3}))))))

I want to have a section of the worksheet that is devoted to tracking their
remaining balances for their leave days and a section of the worksheet that
is devoted to awarding daily attendance points based upon whether they have
any leave left or not.

I finally figured out the formulas...or at least I thought I did. For
example, I want Excel to give the student 3 points for the day if I enter "P"
in cell G2 as long as they still have 1 personal day left to use. Once they
use their one given personal day, I set up the formula to give them 0 points
for every time "P" is entered. My problem is that when I enter a "P" the
second time (and Excel gives a 0 because the student is out of personal
lave), it changes the previous P's 3 points to 0 because their "balance" for
personal leave is now zero.

It all boils down to this...I want to be able to copy the formula to many
cells and have the formula work without changing previous calculations.

Any suggestions would be greatly appreaciated!!!!! (Thanks for reading my
LONG problem!!)

Kate

### Can I make a formula in Excel to display result in same cell?

I am trying to make a cell in which the formula and the entry field are in
the same cell... In other words getting a formula to be locked in a cell to
where whatever info I put in the cell, the locked formula calculates the
entry number.

### Can I make a simple fill-in calculator for webpage using Excel?

I want to make a fill-in calculator for a website using frontpage. It
requires andaverage of 5 numbers tht need to be inputted along with another
number that is used as a multiplier. Average of five numbers times multiplier
(number between 5 and 30 to 2 decimals) times 2% then divided by 12. I can
build the spreadsheet and formula my stumble is getting cells locked down and
transferred to Front page.Using Excel XP and Frontpage 2000

### Vlookup with a sum calculation

I need some help to calculate % of value associated to a country. I have a list of countries with a dollar value in a table. Based on a lookup and a match I need to calculate 4 or 6 % of the value associated to the country name.
Argentina \$ 100
Australia \$ 200
Austria \$ 300

formula i have tried is =IF((A30=K30),SUM(L30*\$G\$29),FALSE) but need a vlookup added to make it work for an entire sheet.

Appreciate the help.Thx

### How to make this formula relative to a labels position?

Continuing on with what I have already learned here, I would like to finish what I have started.

In an earlier thread I learned the “INDEX/MATCH” technique to make a formula “look” for a position in a column of data. This technique is really slick and automates my formulae, partially.

What I am trying to accomplish is to build a formula that calculates its result based on its position relative to two cells, regardless of where the formula’s position is….

There is an example sheet attached.

So-far the formula looks up the column A and finds a value and compares that value to the count in another range.

In the example sheet the formulas I am attempting to fix are the “checksums”

The first table in the sheet is an extraction from the main database that I use to generate the second table.
The second table is a table I manually alter according to observed patterns and methods.
The third, fourth and fifth tables are the unique records extracted from the second table in preparation for insertion back into the main database after correction.

The checksum formulas simply keep me aware of problems across the tables.

The problem is, all of these tables vary in size from day to day….

For example, The check sum found in cell A325 is as follows
```This formula counts the records found in the range E320:E327 via the,
“COUNTA((E320:INDEX(E320:\$E\$984,MATCH(TRUE,E320:\$E\$984="",0)-1)”
Section of the code, and then looks up column A and find a match to the “Actual Repairs” reference and then
subtracts the value underneath that reference from the count made previously in the formula.
“INDEX(A:A,MATCH("Actual Repairs",A:A,0)+1))”Thanks to NBVC I now understand both of these
techniques. After employing them however I notice they still do not do what I want them to do.The first half of
the formula is where I need help. I need the formula to look 5 cells to the right and 1 cell down (cell E320) relative to the
“repairs” reference in A319.
This needs to be a static reference, in that it needs to look 5 cells over and 1 down, every time regardless of where the
formula is.Anyone have insight here?Thanks for your help.

```

### Trying to make a formula for percent if greater than a value

I am trying to create a formula that will calculate percent survival.
Basically I have data in cells and I want to make a fomula that tests if
there is a value greater than 0. I want my answer to be not the values in the
box, but the number of cells with data greater than zero divided by the total
number of cells.

Then, I'd like to write another formula to average the values in those
cells, but only if those cells have a value greater than zero. Can anyone
help?

### Make a Fresnel Zone Calculator

I wantt to make a calculator with excel. I know some excel basics, but
I need help with the formula in the link below.

http://www.softwright.com/faq/engine...Clearance.html

I would also like downtilt and azimuth calculators from the link
below.

http://www.wisp-router.com/calculators/fresnel.php

Again I need help with the formulas. I could use these but I need them
offline also. This is where excel comes in.

I know how to ste the name fields and have an area for the value to be
entered, the formula is killing me.

Thanks

Slim

### I am trying to make a formula that may need LOOKUP,INDEX and MATCH functions.

I am trying to make a formula that will look up peoples names and their relevant info. They are sorted into groups on sheet1 ,Named group 1 , group 2 ect. On sheet 2 when I enter the group name in cell C4 I need to have a formula list up the peoples relevant info like if they are male or female, Birthday and a few others as well.

Then on sheet3, I only want to enter a single persons name and have their data be brought up.

I have simplified the workbook, as there is a lot more info to enter other than birthdays ect, plus there are thousands of peoples data that has to be entered, so I need to be able to expand the lists ect as the list of people grow.

TIA.

### Calculation within a single cell

I would like to be able to enter a number into a cell and have it automatically make a simple calculation within that cell. For example, I would like to enter a value into a cell and have that number divided by 20 and then expressed as a percentage. So if I entered, for example, the number 15 into cell A1, it would automatically calculate 15 / 20 = .75 and show the number 75 in the cell (still A1). It seems to me that there should be a way to do this but I can't for the life of me figure out how. Any help in this would be GREATLY appreciated. I don't want to use multiple columns with formulas to accomplish this because it will make the report (which is for clients) too large and ungainly looking. Thank you again to anyone who has any ideas!

### Formula: Find Number in Range & Calculate If Found

Hi,

Apologies for the poor thread title, I just couldn't think how to describe what I want. I'm looking for a formula, which says if a number is within a range, perform a calculation. I have a table of ranges (0-6000,6001-10000,10000-12000, and so on). My formula looks at the current month cumulative balance, and inserts the value into the line of the range it falls into. However, the formula now contains lots of nested "IF" statements, due to the number of ranges I have. Also, if my cummulative balance is 6005 for example, but was 5005 in the previous month, I would like to see 95 in the 0-6000 row, and 5 in the 6000-10000 row. I making a complete nonsense of trying to explain myself, so I'll attach a quick example of what I'd like to see. Can anyone help?

Rico.