# Excel 2013 :: How To Assign Monetary Value To Number / Quantity

Jul 29, 2013

I am building a spreadsheet for contractors to use to submit home rehab bids. I am using a Windows 8 and Excel 2013. How to assign a monetary value to a number/quantity.

For example, when the contractor indicates 2 toilets need to be replaced and 1 toilet needs to be repaired, I would like to assign the value of \$300 to replace a toilet and \$150 to repair. Ultimately, I would like the 2 and the 1 to be automatically totaled to read as \$750 in the total line at the bottom of the table.

## How To Assign Monetary Value To Percentages?

Jan 6, 2010

I'm brand new to this forum, so please forgive me in advance. I am hoping someone might be able to point me in the right direction. I got a request from my boss and it's something I've never done in Excel and far more advanced than anything I've tried to do.

In my spreadsheet, Columns B-BD are server names, and Rows 2-13 are program names. Inside the corresponding cells all have to display as percentages, and we are trying to display what percentage of each server is being used by each program. In Row 14, each column must total to be 100%. That part is easy, I already have that all setup.

However, the next step requires that each server is assigned a monetary value - one of two monetary values for Virtual or Physical server. Then, somehow I need Excel to calculate the monetary value for each percentage.

For example: if Column B is Virtual, and Row 14 totals up to equal 100%, it also equals \$1,000. Say Cell B4 is equal to 50% and B5 is equal to 50%, each cell is also equal to \$500. Easy enough in theory, but how should I execute this so that these cells stay in % format, but Column BE titled "Total Cost" displays the monetary value for each Program (row)?

I'm pretty sure there will be some kind of formula so I guess that's what I'm asking... how to calculate it?

I'll attach a screen shot to show you the gist of how it looks so far ...

## Monetary Values From Web To Excel?

May 18, 2010

Is it possible somehow to "download" specific values from web into specific cells?

I'm creating some currency converter and problem is that values are changeable every day multiple times, so I wana avoid that with entering specific value of specific currency from web into adequate cells.

This site is mine "database": link

## Excel 2013 :: Formula To Auto Delete Number In Dropdown List When Number Appears In Another Cell

Apr 27, 2014

I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.

## Excel 2013 :: VLookup Stops Working After Certain Number?

Jan 28, 2014

Excel 2013

Our company has a shipping report spreadsheet that has the job number in column A and the ship date in column F. I want to track other things on a separate spreadsheet. So far, we are only down to row 1440 so in order to account for future jobs, my code on the other spreadsheet is:

=VLOOKUP(A2, '[Shipping Report.xlsx]Monthly shipping schedule'!\$A\$1:\$F\$5000, 6, FALSE)

Also, the jobs are arranged by ship date, not by number order.

This has worked perfectly for months but for some reason, it will no longer recognize job numbers greater than 1331. The format is the same between the working and non-working cell. Is there a max limit for vlookups that I am not aware of? If this was a sudden failure, I could nail it down but there has to be an underlying reason I'm not seeing.

Here is a sample of my spreadsheet:
Ship Date
1325 12/27/13
1326 3/10/14
1327 1/4/14
1328 1/31/14
1329 3/3/14
1330 1/22/14
1331 1/15/14
1332 #N/A
1333 #N/A
1334 #N/A
1335 #N/A
1336 #N/A
1337 #N/A
1338 #N/A
1339 #N/A
1340 #N/A

## Excel 2013 :: Create New Values Based On Number In One Cell?

Jul 21, 2014

I have the following data set with about 500 rows. It has one large value in column A, followed by a number between 1 and 24 in column B.

I am trying in Excel 2013 to develop a formula that will: divide A by the value in cell BPlace in cell C, D, E, F... the result of this division, where the number of times the value is repeated depends on the value in B. E.g.

Example of source values/data set:
A
B

18504
18

2011
2

23694
24

An example of the output result that I am looking for:
A
B
C
D
E

2011
2
1,005.5
1,005.5

In above example, the value in column B is 2, so the result of the division of value A with value B is repeated twice. In case value B would be 18, the value would be repeated 18 times...

I have been trying to do this in Excel for more than 2 hours, but I did not manage to complete it.

## Discount Prices Based On Order Quantity And Package Quantity

Nov 23, 2009

I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate:

Sheet 1
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6

Sheet 2
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6...........................

## Match Part Number Then Extract Quantity Onto A Different Sheet

Apr 3, 2014

I have 2 sheets in a file. One is given to me from our parts department containing all parts on a given truck model. The 2nd sheet is exported as a database file from inventor and imported as an update able sheet in excel.

I am needed to compare part numbers between the 2 sheets and the qty of each. So far I have it where it matches the part numbers and turns the ones I don't have in the model red. Now I'm trying to match the part numbers and pull the qty of said part from sheet 2, the imported one, and compare it to the to the qty in the 1st. All I really need to accomplish is matching the part numbers then copying the qty from sheet 2 to sheet 1 onto a new cell of the matched part number.

## Printing PDF Files Quantity In Excel?

Apr 24, 2014

So i have working printing macro code in excel that sends files to selected printer based on last 3 characters, when i click a button only 1 file per click is send to printer but i want to send more files (quantity is listed in row D). I think i need to add quantity row to shell command for printing so it will send not only name of pdf file but quaintity too:

[Code]....

Here is a rough look in excel and rows : PDF.jpg

And here is whole code:

[Code] ......

## Calculate Number Of Days Between 2 Dates And Then Assign Number Based On Answer

Mar 22, 2014

How to create a spreadsheet with what I think will be a very simple formula?

If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2.
If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2.
If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.

OR

Another, maybe simpler, way of saying it is:

If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2.
If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2.
If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.

## SUM Monetary Transactions Based On Two Criteria

Feb 20, 2014

I want to SUM monetary transactions based on two criteria.

1.) if the transaction occurred within a certain month (Jan)
2.) based on the transactions category ("Obligations")

I have two formulas which successfully validate the data individually but I need to combine them so that both criteria must be met before data is summed.

=SUMIF(REGISTER!E3:E1000,"Obligations",REGISTER!G3:G1000)

=SUM((IF(MONTH(REGISTER!\$B\$3:\$B\$1000)=1,REGISTER!\$G\$3:\$G\$1000,0)))

## Excel 2010 :: Pull Quantity Based On Date Range

Oct 27, 2011

Using excel 2010

In cell U24 I have this formula:

=SUMPRODUCT((\$E\$3:\$E\$2000=51014)*(\$F\$3:\$F\$2000=101)*(\$K\$3:\$K\$2000))

Currently, as you can see, this is summing up the criteria specified from row 3 to row 2000...(which i made the last row 2000 becuase I will never exceed row 2000 with info)

What I am trying to do is in cell T22 and U22 be able to plug in date ranges and make the above formula give me the sums of thos date ranges.

For example in T22 put in 8-1-11 and in U22 put in 10-1-11, and have the sum of that date range populate in cell U24.

Now ther kicker is this: some dates are repeated so I may have six rows of date 8-3-11....and they will have to be part of the sum total.....and ALSO the dates are not in chrological order....I may have six rows of 8-3-11.......then four rows of 8-10-11.....then maybe another three rows of 8-3-11, and so on and so on...this is for all dates. Sorting the dates in order is not an option.

## Excel 2003 :: Reposition Data And Sum Quantity Based On Matching Reference

Feb 15, 2013

I'm using excel 2003 and using VBA I want to convert data from my original data below.

The first row is just to identify the columns below it.

REFR1,REFERENC2,COMPANYNAME01,CURNTDATE,QUANTITY,ITEMNUMBER,PRICE
71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 TB,65.9900
71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 CH,86.5800
71721,121221142,EXAPLECOMPANY,2-13-2013,1.0000,MPG PB01,75.9900
71718,121234503,EXAPLECOMPANY,2-13-2013,2.0000,MPLC001,146.9900
71718,121234503,EXAPLECOMPANY,2-13-2013,1.0000,MPMC001,120.9900
71720,121238748,EXAPLECOMPANY,2-13-2013,1.0000,MPS007,63.9900

To be converted to the following:

"COMPANYNAME01",
"TOTALITEMS","REFR1","REFERENC2","CURNTDATE","0","0.00","0.00"
"ITEMNUMBER","QUANTITY","PRICE","TOTALPRICE",

"EXAPLECOMPANY",
"2","71723","121222010","2-13-2013","0",,"0.00","0.00"
"MPG TBS01 TB","1.0000","65.9900","65.99",
"MPG TBS01 CH","1.0000","86.5800","86.58",

[Code] ..........

The converted output data above can be within the same document or output to a text file.

## Excel 2013 :: Mail Merging From Excel Database To Outlook

Apr 15, 2014

Using EXcel 2013, Windows 8

I have an Excel worksheet with one column being e-mail addresses. Other columns are Christian names, etc

Ideally can I create a full Mail merge with Outlook using whatever data I want. But probably just e-mail address and Christian name?

Otherwise be able to send one e-mail to all the e-mail addresses, without a major re-type.

## Assign Number To Row ....

May 1, 2009

I have a list of numbers such as:

A01
A23
A53
A64
A74
A128
B01... the goes on too approximatly D128

and i wanted to know if there is a way to assign the value to the row?
For example:
A23 in column A row 23
A53 in column A row 53

is there any method that achieves this?

## Assign Number To Text

Jan 23, 2014

I currently have a spreadsheet which I use to do an audit of the office stationery supplies.

There 3 columns at the focus of this question:

F - Number of items in stock
H - Stock re-ordering level
I - Order Required

I currently have an IF statement to tell me if I need to re-order any stock, by comparing the Number of items in stock against Stock Re-ordering level.

The statement is =IF(H1>=F1, "Yes","No")

This works fine under most circumstances. However, in some of the H cells, I have the text "Special" which indicates that the stock will only need re-ordering on a special occasion.

Any value I enter in F will cause the I to say "Yes".

Is there any way to make the word "Special" equivalent to 0, so I says "No"?

## How To Assign Number To Course Name In Input Box

Jun 26, 2014

In column 'o' I have inserted a number for course name in column 'p'. I want to assign a course name to that particular number as follows:

Column 'o' Number ... Column 'p' Course name
1: A. B. C.
2: J. K. L.
3: P. Q. R.
4: X. Y. Z.

And so on as per course names.

If I enter number 1 in inputbox then I want to enter the course name in column 'b4' respectively.

## Assign Week Number

Jul 12, 2006

I have a list of random dates between 09/01/05 and 07/01/06. I would like to assign a week number to each date. For example, I would like it to be week 1 if the date is between the dates of 09/01/05 and 09/07/05. Is there any simple way of going about this?

## IF Statement - Assign A Number Value Of 1 To All The Records?

Aug 6, 2014

I have a medication start end and admission date.

and i want to assign a number value of 1 to all the records that the medication start date is 2 days after the admission date. How do I do this using the ifs function.

## Find Cells With Same Value And Assign A Number To Each

Jun 11, 2009

I use my spreadsheet to keep score during tournaments. when i calculate my leaderboard at the end of the event i list the results first place all the way down until it finishes the list of players. i need to be able find players with the same or equal score and list them as tied for the same place.

another words if i get to the 3rd place and i had 3 players tie for third i need my leaderboard to show them all in 3rd place . currently it would number then 345. i listed below how it should format it. i also attached a file it will make it more clear.

## How To Assign Week Number Against Dates

Apr 15, 2012

We have started a new venture from 5th April & I need to create a template for a year and assign week no against the dates. The week1 will start from 5th April & week 52 will end on 31st March.

Also there will be a summary of the Week No(Starting Date & End Date) against each week at the top of the template. I could put the formula for the summary part but I am unable to figure out how to assign week no against the dates as given below. It is very difficult to assign the week no manually for the whole year & also it is vulnerable to error. a formula across B7:B23 which can deliver the desired result?

Sheet2  ABC1Week NoStart DateEnd Date2Week15-Apr8-Apr3Week29-Apr15-Apr4Week316-Apr22-Apr5   6DateDesired
Result 75-AprWeek1 86-AprWeek1 97-AprWeek1 108-AprWeek1 119-AprWeek2 1210-AprWeek2 1311-AprWeek2 1412-
AprWeek2 1513-AprWeek2 1614-AprWeek2 1715-AprWeek2 1816-AprWeek3 1917-AprWeek3 2018-AprWeek3 2119-

[Code] .........

## Assign Unique Number To A String

Jun 4, 2008

I'm currrently using column I and col J

in col I are all the units of measurement: KG, PKT, BAG
in ColJ is the formula to return:

KG as 9999999
PKT as 8888888
BAg as 7777777

if(i2=kg, 999999999,"false!")

i was thinking of using if....but triple if formulas would be long isn't it?

PART2 of this would be using:
=IF((SUMPRODUCT(--(db!\$A\$2:\$A\$36000=\$B\$1),--(db!\$F\$2:\$F\$36000=C\$5),--(db!\$G\$2:\$G\$36000=C\$6),--(db!\$E\$2:\$E\$36000=\$A7),--(db!\$D\$2:\$D\$36000=\$B7),db!\$I\$2:\$I\$36000))=9999999,"KG","Invalid")

to check 5 values and if it is 999999999 it'll return KG
8888888 PKT
7777777 BAg

## Assign A Number Value To Text Within Worksheet

Sep 10, 2009

I need to count the number of times a value appears in a row and assign a number that tells which occurence of the text it is.
For example

Jim 123 Smith Street
Jane 123 Smith Street
Bob 543 Apple Street
Mary 543 Apple Street

I would like a way to insert a column that puts a 1 next to Jim and a 2 next to Jane, a 1 next to Bob and a 2 next to Mary and so on.
Ultimately, I want to sort the list and delete all the twos, thus deleting the duplicate address entry.

## Count Total Number Of Saturdays In 2013

Apr 13, 2012

the closest appears to be the number of Fridays in a particular month.

What I need to calculate is the number of Saturdays in each year from 2013 through to 2022 (by year), and the same thing for number of Sundays. I presume that it will be 52, however if a year starts on a Sunday and it is a leap year then there could well be 53.

I tried =SUMPRODUCT(--(WEEKDAY(YEAR(C1)=7))) (where C1 has 2013) to try and calculate the number of Saturdays in 2013, but it didn't work.

## Covert Number To Date 20131126 To 11/26/2013?

Jan 1, 2014

Covert number to Date 20131126 to 11/26/2013

## VBA To Count Occurrences On A List And Assign Number

Jun 6, 2013

I have found ways to count cells but what I am trying to do is in column F I have a list of meeting topics, and sometimes these repeat in a year. in my drop down menu I have all of them listed however my supervisor wants me to add a count after the meeting number in the 1_1X format where x is the number of times a topic has been used.

The output will be added to my macro here

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim y As String
Dim z As String
Dim b as Integer

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("MEETINGNUMBER")) Is Nothing Then Exit Sub

[Code] .......

## Using RAND Function - Assign Number From 1-5 To Each Of Identity

Apr 29, 2014

I have 379 sample identities in my Excel spreadsheet in A2:A380. I want to randomly assign a number from 1-5 to each of these.

## IF THEN Statements: Assign A Letter Grade To A Number

May 9, 2006

I have figured out how to assign a letter grade to a number, but am having trouble assigning it the other way, a number to a letter grade. For instance: If a student gets an A, I want the column next to it to indicate that the A represents a 4; a B represents a 3; a C represents a 2; D a 1; and F a 0. This will allow an easy grade point average calculation.

A 4 History
C 2 Math
A 4 English
B 3 Physical Ed
D 1 Science

GPA 2.80

## Assign Duplicated List Entries Same Number

Mar 4, 2008

I have a long list of items. I would like to assign the same number to each of the replicates. I've been trying to expand on this: = COUNTIF(\$A\$1:A1,A1). which increments the number for each replicate, but don't seem to be getting anywhere. The attached file might make it clearer what I'm after.

## How To Use Custom Number Formatting To Get 20130522A To Display As 2013 05 22A

May 22, 2013

I can't figure out how to use the custom number formatting to get 20130522A to display as 2013 05 22A. I don't care if it has to be 2013 05 22 A, the extra space is fine.

I have been googling and trying different things and I cant get anything to work. All of the characters can be text, there will always be 4 characters, a space, 2 characters, a space, and the remaining 3 characters. It seems as though it should be so simple to add two spaces into a fixed length string, but I guess not.