#### Looking for a formula

I have the following problem and looking for a formula

a1 = price
a2 = cost
a3 = proposed cost
a4 = percentage rate

I would like to calculate my proposed cost which is a1*a4. However, if the
real cost in a2 is > a3 I would like to have a3 = a2 as I would want to
carry over the real cost.

Thanks for your help.
Gerd

 0
12/13/2003 3:39:55 PM
excel 39879 articles. 2 followers.

2 Replies
570 Views

Similar Articles

[PageSpeed] 59

=if(a2>a1*a4,a2,a1*a4)

--
Regards,
Tom Ogilvy

Gerd <gerd.goebel@nospambavarian-cons.com> wrote in message
news:uCKjd9YwDHA.1576@TK2MSFTNGP11.phx.gbl...
> I have the following problem and looking for a formula
>
> a1 = price
> a2 = cost
> a3 = proposed cost
> a4 = percentage rate
>
> I would like to calculate my proposed cost which is a1*a4. However, if the
> real cost in a2 is > a3 I would like to have a3 = a2 as I would want to
> carry over the real cost.
>
> Thanks for your help.
> Gerd
>
>
>

 0
twogilvy (1078)
12/13/2003 3:54:13 PM
Thanks Tom

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:vtmdgbhddji0cc@news.supernews.com...
> =if(a2>a1*a4,a2,a1*a4)
>
> --
> Regards,
> Tom Ogilvy
>
> Gerd <gerd.goebel@nospambavarian-cons.com> wrote in message
> news:uCKjd9YwDHA.1576@TK2MSFTNGP11.phx.gbl...
> > I have the following problem and looking for a formula
> >
> > a1 = price
> > a2 = cost
> > a3 = proposed cost
> > a4 = percentage rate
> >
> > I would like to calculate my proposed cost which is a1*a4. However, if
the
> > real cost in a2 is > a3 I would like to have a3 = a2 as I would want to
> > carry over the real cost.
> >
> > Thanks for your help.
> > Gerd
> >
> >
> >
>
>

 0
12/13/2003 4:03:49 PM

Similar Artilces:

This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C61A25.29EC1E10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I didn't receive a response on my last post so I worked on my problem = some and came up with some results. Could someone look over my formulas = and tell me if I can achieve this same result any easier? Here is a sample of the worksheet: D E F G H I J K L M N O=20 22 Sun Mon Tue Wed Thu Fri Sat Reg OT x1.5 OTx 2 Rate Total=20 23 off 12 11 11 11 11 off 40 15 1 10.00 \$645.00=20 ...

Can I use the NOW() function in a formula?
I have a cell B1 that has NOW() time and another cell B2 that has NOW() date. I am trying to have data from another cell F1 brought in to the destination cell B3, when a given date and time occur. =(IF(AND(B1="23:00:00",B2="12/03/2009"),F1," ") This formulas does not work. The Clock is continuously active / always changing. Any suggestions would be appreciated. Thanks. The NOW() function returns both a date and a time. Even if you format the cell to display only date/time, all the data is still retained. NOte that if you did want just the d...

Help with formula please.
I posted this a couple of days ago but doesnt seem to have appeared so I hope this is not a duplicate. Original formula from previous post... =(("12:00"-MAX(C11,--"7:00"))*0.3+(MAX("23:00",C11)-MAX(C11,"12:00"))*0.4+(C12-MIN(C12,"23:00"))*0.5)*24 I have changed the formula to =(("12:00"-MAX(C11,--"7:00"))*0.3+(MAX("23:00",C11)-MAX(C11,"12:00"))*0.4+(C12-MIN(C12,"23:00"))*0.5)*C13 As a test I have put in aircon running for 1 hour between 10-11 am So this should return a value of .30 It is ...

Copy and Paste without copying Formulas
I have created a workbook with several sheets. The first sheet consists of a master list of 8000 numbers. The second sheet is where I paste a smaller list of numbers. The workbook performs a search and returns numbers that match on the third sheet. The third sheet also has formulas. I want to be able to copy and paste the resulting info on the third sheet to another workbook without copying any of the formulas......I just want the info. Any suggestions?? Ken Copy>Paste Special>Values>OK>Esc. Gord Dibben Excel MVP On Thu, 18 Nov 2004 14:21:13 -0800, "Ken"...

Selecting X cells based on answer to formula
Hi, I am trying to put together a spreadsheet that performs a couple of tasks. The first is basic resourcing which i am ok with, sort of how many heads and how many hours available means X cases will be done. The next thing I need to do is use the X cases result to identify from a list the oldest date and work through sequentially X dates in that column, which will not be consecutive, and tell me what the date of that case is. So if there were 10 cases done I would like one cell saying the oldest case worked on was - first date in column. And following doing the 10 cases I'd lik...

how do I insert a filename into a formula from another cell?
I have a spreadsheet with a filename in a cell. I would like to reference that filename in a formula. I can't seem to get it to work. "+cell number" doesn't work. Any suggestions? You would normally use INDIRECT to do this, along the lines of: =INDIRECT("["&A1&"]Sheet1!C2") where A1 contains your filename (with the .xls extension) and you are trying to return data from C2 on Sheet1 of that file. However, INDIRECT will only work with files that are open, so you would have to have the file open for this to work. Hope this helps. Pete On...

Array formula
{=SUM((F45=10)*(H45={1,2,3})*{20,10,5})+((F45=20)*(H45={1, 2,3})*{42,21,11})} This formula works fine, up until the separated part, then it just does nothing(I put the spaces in just to clarify the problem area for this discussion). No matter what order put the arguments in, it only works up to the same point. Is this just a case of to much info? How do I extend this formula (I need it to be even longer the above example). thanks. Hi Atom, Try replacing SUM by SUMPRODUCT and do a normal completion with just ENTER since it is not an array formula Bernard "atom" <hmm@hmm.com...

Accounts Payable Look up
The default lookup for a vendor is the vendor number. When the lookup default is changed from by Vendor ID to by Vendor Name - why will the change not stick. Give your customer the choice in how they look up a vendor. If the programmers do not feel that they are capable of this change, have the AP module programmers speak to the GL module programmers who figured this out some time ago. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the messag...

can i create formula giving totals based on financial & text info
Am i able to create a formula that gives me monetray totals for expenditure on hotels, split into totals for 6 varying business sectors? ...

I am looking for a function for linear interpolation
I am looking for a function for linear interpolation Look in HELP for the LINEST() function -- Kind regards, Niek Otten Microsoft MVP - Excel "azad" <azad@discussions.microsoft.com> wrote in message news:C53A752C-DF76-4B9F-9145-6F39495088B7@microsoft.com... >I am looking for a function for linear interpolation ...

How can I look in a sharing folder of another computer?
I have a home network of 4 computers - 2 WinXP, Vista, Win7. The network works normally. I can easily look in a sharing folder of another computer by my Total Commander. But I made a bootable BartPE CD. After booting from the CD I can get online and browse any sites around the world, but I cannot look in a nearby computer folder! Every time I get the following message. "Workgroup is not accessible. You might not have permission to use... " How do I fix this problem? Thank you Lev "Levlg" <levlg@tpg.com.au> wrote in message news:4ba0b9b4\$1@d...

Ability to create formula to calculate benefit or deduction code
Would like to use a benefit or deduction based on a formula to calculate the amount instead of choosing only a flat amount or % as it is currently set up in GP version 9. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Commun...

Using a query versus a table for the look up wizard
Hello, I have a field which I would like to use the lookup wizard for to generate a drop down list for the user to select from. My question is whether my databse would run faster if I used: a query from an already established table as the values for the lookup wizard, or to build a new table to hold the information for the lookup wizard to use. I think you'll find few of us recommend the use of the Lookup Wizard. Lookup fields only have a place in tables, and your users should never be working directly with the tables. Create a form, and use a combo box on the form. (See http...

Results of formula vs real numbers
HI. How do you change a formula to read the value as a result of a formula and not necessarily as a real number? Example: In G6, i have the following formula that gives me the hour of a time in F6 =IF(F6>0,MOD(F6-"1:00",1),"") F6 contains 00:30:08, The result is 23 In another cell, I am using this formula, but it doesnt recognize the 23. =SUMPRODUCT(--(B6:B58331="james"),--(G6:G58331="23")) The 23 is actually the HOUR of time so it isn't a whole number or real number persay. It is a rounded time to the hour. Yes, but it isn't a text ...

protect formulas without password
is there a way i can protect the formulas without protecting and passwording and identifying various ranges? the user accidentally enters data into a calculated cell and wipes out the formula. thank you, mike g Mike, Set the cells that the user can change to unlocked using Format, Cells, Protection, set those they can't to locked. Protect the Sheet. Robin Hammond www.enhanceddatasystems.com "work" <mike@radiant.net> wrote in message news:42140299.10B3@radiant.net... > is there a way i can protect the formulas without protecting and > passwording and identifying ...

Ignoring #DIV/0! in a formula
Is it possible to ignore a cell in a formula if the data is #DIV/0 ive tried messing around with IF statements but I cant seem to figure it out =IF(ISERROR(cellref),erroraction,noerroraction) =IF(ISNUMBER(cellref),noerroraction,erroraction) You can also use it in range functions, such as =SUM(IF(ISNUMBER(range),range)) but you must array enter (Ctrl-Shift-Enter) Jerry DLZ217 wrote: > Is it possible to ignore a cell in a formula if the data is #DIV/0 ive tried > messing around with IF statements but I cant seem to figure it out "Jerry W. Lewis" <post_a_reply@no_e-ma...

If, say, =SUM(I2:I10) totals "0", how to change formula to put a "0" in I11?
I know this is going to be simple but everything I've not got anything to work. How can we change formula, pls, to have "=SUM(I2:I10)" show up as "0" in I11 if there aren't any values anywhere in I2 to I10? Thanks. :oD If there are no values to sum the formula should already be returning 0. What result are you getting? Biff "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message news:OV\$7dJLnHHA.3704@TK2MSFTNGP02.phx.gbl... >I know this is going to be simple but everything I've not got anything to >work. How can we change f...

Look up values and place in spreadsheet
I have an excel workbook with two sheets. On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. Try this *array* formula in A2 of Sheet1: =INDEX(Sheet2!\$B\$1:\$B\$30,SMALL(IF(Sheet2!\$A\$1:\$A\$30=\$A\$1,ROW(\$A\$1:\$A\$30))...

coping formula
I am trying to copy a formula down a column. When I copy the formula down I want the cell to be blank until i fill out the cells to give me my balance. My three columns are Debit / credit / balance. My formual reads : f7(balance column)+e8(credit column)-d8 (debit column) when i copy the formula down form my balance column it gives me the balance in every cell. I would like for it to be blank until i fill in my debits and credits. I hope you understand the way i wrote this thanks much -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200803/1 You ...

MATCH formula #2
I have a spreadsheet that lists all the types of machines and thei model numbers. When a customer places an order for a specific machine I need to reference the other workbook in order to get the model number Is there a way to have this done automatically. For example: when enter a machine type (CE-10), I want the model number for that machin to be entered automatically in another cell, without having to open th other workbook and copy and paste the number myself. I considered dat validation (using a list) but can't reference another workbook. Any thoughts? Thanks, De -- da ------...

Formula Cell not updating
I have a workbook with several worksheets in it. The sheets are not related to each other except that I used the copy worksheet feature to add sheets. There is a formula cell which totals the two cells to the immediate left on each worksheet. For some reason, when I change one of the values in the cells being added, it doesn't update the total, unless I retype the formula in the cell. I have had this problem one other time in the past. I'm not sure how to fix it. -- Sincerely, Beverly76 Hi you may check if automatic calculation is enabled ('Tools - Options - Calculate...

Excel: Replace strings with a Matrix formula
Hello NG, the following sheet: - In column A are words in German. - In column B are the translated words in English. - In C1 is one English clause with only one single German word in it. ToDo: I want translate this word in English and write it o D1. But I dont want to use VBA or more then one cell for the calculation. Such solutions I can make for myself. I'm working since several years with Excel and VBA. Currently I'm playing with this matrix formula. (I have translated the Excel function names from German to English, but I dont know if I used the right words.) {=IF(ISERROR(SEARCH...

>< formulas
A spreadsheet containing two colums. Column A has dollar amount entries. Column B has date entries. These entries are entered throughout the month and every month of the year. I need a formula that will sum the amounts in column a only if they are during the month of march from column b (for example) "mpiton" <mpiton@discussions.microsoft.com> wrote in message news:F1965B8B-EF82-41A6-89CF-86FF72706DB2@microsoft.com... > A spreadsheet containing two colums. Column A has dollar amount entries. > Column B has date entries. These entries are entered throughout th...

Excel formula help #5
I've come up against a problem in Excel, I need help to figure out a formula. A B C D 1 Number Of Months 24 �60.00 �1,440.00 2 Number Of Weeks 96 �15.00 �1,440.00 I will try to make this clear to understand, what I need is when I change cell C2 either adding more money or taking away, I need cell B2 to reflect the change either increasing the amount of weeks needed to get to D2s total or decrease depending on what was changed in C2. Any advice or help would be much appreciated. On Oct 10,...

Lookup Formula
I have an Excel sheet that shows hourly rates based on the number of hours a customer purchases. Example Column A Column B Column C (Hours)From: (Hours)To: Hourly Rate 1 40 \$35 41 80 \$32 81 120 \$30 I have a seperate column under a different tab where a sales rep enters the total number of hours. I would like the formula to automatically lookup the chart above and show the appopriate hourly rate. Thanks, Donne Hi - best to ask the Excel gurus in an Excel n...