Custom addition formula
Using Office2000 Sp3
I use Excel a lot for historical things including eg; areas of land
expressed in ACRES ROODS PERCHES which is not added in tens (similar to
�/s/d). 40 perches = 1 Rood, 4 Roods = 1 Acre, 160 perches=1 acre. I have a
formula which when applied to entries across 3 columns gives a decimal
answer. The formula is =SUM(((D1*160))+(E1*40)+F1)/160 assuming acres are in
D1, Roods E1, perches F1. Once this is decimalised I can work out ratios
However, I occasionally get tabbed text files or excel files from people who
have entered lots of raw data as eg ARP where it would be ...How to: text color depend on value
Anyone know how to make the text change colors, depending on what valu
certain data is? I'm sure it's easy...
bobsmith58d's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1452
View this thread: http://www.excelforum.com/showthread.php?threadid=26148
> Anyone know how to make the text change colors, depending on what valu
> certain data is? I'm sure it's easy....
Go to Format/Cells/Custom and inside the "Type" box, choose th...How to determine the values? 06-05-10
Does anyone have any suggestions on how to determine the value?
Under following columns:
[A] [B] [C] [D]
1 174 198 222
7 180 204 228
9 182 206 230
A given number is 204 in cell E1, I would like to determine the value under
column A, which should return 7, because 204 is under the same row.
A given number is 174 in cell E1, I would like to determine the value under
column A, which should return 1, because 174 is under the same row.
Does anyone have any suggestions on how to do it in excel?
Thanks in advance for any suggestions
Try this array formula** :
...How do I sum values that may contain "#N/A" in Excel?
or remove the cause that give the #N/A result
"bakerbabe" <email@example.com> wrote in message
enter as an array formulas - use SHIFT+CTRL+ENTER
Bernard V Liengme
remove caps from email
"bakerbabe" <firstname.lastname@example.org> wrote in message
>...Rounding Value Up to Whole Number
I have a report footer that I'm adding values from the detail section. Many
of my values that I'm adding are .333. Once I sum the value in the footer, I
end up with not a whole number, for example 9.999. Is there a property I can
set to bring the value to the next whole number. Thanks for your help
=- Int( - [MyField])
(note there is a - before the Int and before the my field)
Substitute the name of your own text box)
int(number*100 + 0.5) / 100
"PowellGirlTN" <PowellGirlTN@discussions.microsoft.com> wrote in message
news:CBDE76F7-8837-4223-BF5C-E...How I can pick a specific value on an array in excel
Given a table with A to H columns by 1 to 20 rows return an specific value. I
would like to enter column ID and row ID to get back the value where both
> Given a table with A to H columns by 1 to 20 rows return an specific value. I
> would like to enter column ID and row ID to get back the value where both
...Purchase Order Formula
After recently finding out that the Shipping Field does absolutely nothing
in a purchase order reguarding Costs of Goods, or anything for that matter, I
decided that "oh, I can just use a Formula to change the costs in my purchase
order to reflect my shipping costs!" So, after going through and adding up
all the quantities of items recieved, and dividing it by my shipping cost, I
got a cost of $1.40/item. No problem right? Wrong. The Formula will in no way
reflect a NEWLY updated cost. And I cannot use Formulas after the purchase
order is committed!!!!! >:O
FOR EXAMP...Receivables Statement Ranges
How can I get the User Defined Field 2 into the drop down box for Ranges on
the Print Receivables Statements page? I see the customize User Defined
Field 1 in there but not the second after I have customized it and added data
into the field. Is there something I am missing? Thanks!
No, the first field is automatically provided in the search but not the
second. This would require a modification by a programmer
Richard L. Whaley
Author / Consultant / MVP
Documentation for Software Users
Get our Free Tips and Tricks Newsletter and check out our books at
http://www.AccoladePublicat...Outllok Rule Creation based on Greater Than Numbers...
Program: Outlook 2003
My boss has requested the creation of a particular type of rule that has me
stumped. He receives many quote requests per day but we are only interested
in them if they are BELOW a certain quantity, i.e. we would be interested in
fulfilling an order for less than 1000 but not more than that. I have tried
filtering based on "Specific words in the body" with entries such as
">1000," (hoping Outlook would know to treat the entry as a number above
1000, also tried "1,***," (hoping it would recognize as a number
w/wildcards)...Copying formulas #12
I have a row of forumulas, which relate to columns of data. Each formula
needs to be copied down the sheet, but I need the column value to change, not
the row value.
eg C3 : =sum(c2:c31)
C4 : =sum(d2:d31)
How can I replicate the formulas down, but get the Column values to change ?
Ignoring the issue of Circular references (the formula in C3 references a
range that includes C3)...
I *think* this will work for you
Put the formula =SUM(C$2:C$31) into an empty row in your spreadsheet and
copy it across to all the columns you want to su...help with if formulas
I think that this is fairly simple but I still need help. Could anyon
tell me what formula to use if I want to have column d show me a valu
of 8 if the value of column c is greater then 5 hrs but show me a valu
of 0 if the column c is less then 5 or blank?
rvnwdr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2390
View this thread: http://www.excelforum.com/showthread.php?threadid=38119
format the result as time. If C1 has 5 hrs as integer 5, do not ...field returns a value if at least one matching record is found
I had posted this question in the Forms thread, but that was probably the
My query for PartNumInfo brings together lots of different tables including
customers, contact info, status of jobs. A feature I need to add is a warning
if at least one defect report exists for a given part number. It would be
shown on the form as a conditional format of the text box where the part
number is shown.
So I would like to add a field to my query that would return some value
(like the part#ID) if at least one Defect Report exists for a part#. I
created a sepa...formula Help needed
I have two worksheets one is purchases and another is named Bank
on purchases I have 4 columns
Then I have Bank
What can I put in C3 to lookup for �480.18 in Bank worksheet and return the
value in C3?????
hope I explained it clearly..
Do you just want whatever is in the one cell to appear in anothe...Suppress Main Section Based on Subreport
In Crystal XI, I want to suppress sections of the main report when either of
the two subreports that I have on my main report are blank.
Specifically, each of these two subreports does not always show data.
That's because I have configured the one and only field in each subreport to
suppress itself when that field is greater than or equal to 0.
In any case, if either of these two subreports are not displaying data, I do
not want sections of the main report to display data. And, that's true if
those sections contain either of these two subreports, any oth...formula doesn't work
I am using window XP, have column B, C and D. Cell C1 and B1 are the numbers.
I try to enter a formula into column D, it keeps give me C1/B1*100 in cell
D1. Why it won't give me the numbers in D1? Is this my Exel's setting problem?
HTH. Best wishes Harald
"JJ" <JJ@discussions.microsoft.com> skrev i melding
> I am using window XP, have column B, C and D. Cell C1 and B1 are the
> I try to enter a formula into column D, it kee...Avoiding Nested 'IF' Statements
I have seven cells (A1 - A7) with drop-down lists that allow the user to
select one of a number of text options. The default text option is the word
I have an eighth cell that needs to display a text message whenever any one
of the seven cells is displaying any text other than "None". That is, if
one of the seven cells is NOT "None", then the message should display.
Is there a more elegant formula for achieving this, other than nesting lots
of 'IF' statements?
-Please remove 'safetycatch' from email address b...Increment a number based on four adjoing cells
I am using an xlsx spreadsheet for indexing metadata for file folders. Based
on the metadata selected from dropdowns in columns H, I & J, the spreadsheet
builds a file number in the format AA-AA-NN-nnnnn (A=alpha; N=predetermined
number; nnnnn= sequential number).
I'm using the following formulas:
Col B =VLOOKUP(H577,FUNCTIONLU,2)
Col C =VLOOKUP(I577,CATEGORYLU,2)
Col D =VLOOKUP(J577,TYPELU,2)
=IF(D576="","",IF(COUNTIF($D$2:$D576,$D576)=1,TEXT(0,"00001")+0,IF(COUNTIF($D$2:$D576,D576)>1,INDEX($E$2:$E576,MATCH($D576,$D$2:$...Default site on SOP transaction based on order type?
We have an inspection site set up that we use for all material that is
returned to us. I'd like to customize SOP entry so that the site id defaults
to this inspection site for returns. Otherwise the standard defaults for the
site id would apply. Has anyone done this type of customization? Any
suggestions / pointers would be appreciated.
VBA would enable you to add this functionality. The code is fairly simple.
Charles Allen, MVP
> We have an inspection site set up that we use for all material that is
> returned to ...Excel formulae start doing strange things
Has anyone had the problem where the result of a formula is incorrect.
I've had on occasion entered a simple formula, such as adding a group
of numbers within an "IF" statement and the result is wrong. If I start
a new workbook, and enter the exact same thing, it works out correctly.
Yesterday one of my co-workers had a problem with a spreadsheed she's
been using for years. All of a sudden, she started getting "VALUE"
errors in the lower half of the spreadsheet. All formulae were the same
in each row. I ended up fixing the problem by deleting 5 rows, then
entering ...VLOOKUP formula displays in cell, will not return data
I had an older version of Excel (97 maybe?) and our office updated to 2007.
Now my vlookup will not return data. The vlookup formula appears in the
cell or I get the NA#. I've formatted my lookup cells and table to General
but still get the same result.
After formatting the cell as General, re-enter it (F2/Enter) - if you're
still seeing the formula, press Ctrl/~ -- you're looking at the formula layer
instead of the value layer.
> I had an older version of Excel (97 maybe?) and our office updated to 2007.
> Now my vloo...Count certain values
I have a spreadsheet that has column A with names, column B with their
choice of meal.
What I want to do is count how many of each dish are required (how many
steaks, chickens to order)
Can someone please show me the formula?
Probably the easiest way would be to create a "master, unique" list of all
available meals, say in Column C.
Then, with names and choices filling A1 to B100, try this in D1:
And copy down as needed.
--------------...Importing text to chart based on link to cell
I am attempting to link a cell from my spread sheet to a
text box in my chart, but have been unable to do so. Does
anyone know how or if this is possible.
On Fri, 7 Nov 2003 12:59:29 -0800, Stephen <email@example.com=
> I am attempting to link a cell from my spread sheet to a
> text box in my chart, but have been unable to do so. Does
> anyone know how or if this is possible.
Select the chart, then click in the formula bar (above the chart, next t=
the little "fx"), type the equal sign, then click on the cell that you =
wish to refe...increase the timeout value of DSAccess
How can I increase the DSAccess timeout setting? I know that DSAccess in
Exchange 2003 keys on one specific GC, and if that GC is unavailable to it
an event will be recorded in the application event log. But how often does
Exchange query DSAccess and can I increase the timeout value, if any?
Actually, DSAccess will use as many GCs are available within a site (up to
10) and will load balance between them. It will use only one configuration
DC for write activities. This makes sense given that there is potential for
replication conflict if writes are made...sum value then insert row
How can I make for loop?
the conditions are: if total value column height = 5 and column
Article = "bike" then copy second row to row 6 (therefore 1 row
added between file 5 and 6)
thanks for your response
...Expression for Percentage Calculations Based Upon Subsets of Field
How do you create an expression that will calculate a percentage based upon
subsets of two fields?
For example, one field is "Status" (there are three options), the other is
"Resolved" (populated with a "Yes" in the appropriate cells that correspond
to those "Status" entries that are resolved. I must calculate the percentage
of "Resolved" entries for each status classification based upon the total
number of each status classification.
I have created parameter queries that appropriately identify the number of
matching records for each S...