IF formula to round up values depending upon their outcome

I am using an IF formula to calculate between two cells, one is J (width) the 
other is K (length).  Currently these formulas give an answer that then has 
to be rounded up based on the decimal place.  I need the formula to also 
round up the amount to quarter increments.  For example if the answer is 1.17 
then the formula needs to make it 1.25, if it is 1.33 then the formula needs 
to make it 1.5, and finally if it is 1.63 then the formula needs to make it 
1.75.  So how do I add or make the formula round up to quarter increments?  
The formula that I am using is:  
=IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))
0
JMMc (2)
5/23/2008 6:13:03 PM
excel 39879 articles. 2 followers. Follow

7 Replies
636 Views

Similar Articles

[PageSpeed] 59

Tty:

=IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"JM_Mc" <JMMc@discussions.microsoft.com> wrote in message 
news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
>I am using an IF formula to calculate between two cells, one is J (width) 
>the
> other is K (length).  Currently these formulas give an answer that then 
> has
> to be rounded up based on the decimal place.  I need the formula to also
> round up the amount to quarter increments.  For example if the answer is 
> 1.17
> then the formula needs to make it 1.25, if it is 1.33 then the formula 
> needs
> to make it 1.5, and finally if it is 1.63 then the formula needs to make 
> it
> 1.75.  So how do I add or make the formula round up to quarter increments?
> The formula that I am using is:
> =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))
> 


0
sandymann2 (1054)
5/23/2008 6:22:13 PM
Thank you so much this has solved a question that has been around for quite 
sometime.  Could you answer another formula for me.  I have two cells that I 
need to sum up, one cell has a set value (E), and the second cell is a value 
that is only entered when needed (F).  How do I sum up the two only when F 
has a value in it?  (If there is no value in (F) I want the formula to not 
place (E)'s value as the sum).



"Sandy Mann" wrote:

> Tty:
> 
> =IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))
> 
> -- 
> HTH
> 
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
> 
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
> 
> 
> "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message 
> news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
> >I am using an IF formula to calculate between two cells, one is J (width) 
> >the
> > other is K (length).  Currently these formulas give an answer that then 
> > has
> > to be rounded up based on the decimal place.  I need the formula to also
> > round up the amount to quarter increments.  For example if the answer is 
> > 1.17
> > then the formula needs to make it 1.25, if it is 1.33 then the formula 
> > needs
> > to make it 1.5, and finally if it is 1.63 then the formula needs to make 
> > it
> > 1.75.  So how do I add or make the formula round up to quarter increments?
> > The formula that I am using is:
> > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))
> > 
> 
> 
> 
0
JMMc (2)
5/23/2008 6:39:01 PM
Try:

=IF(F41="","",SUM(E41:F41))

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"JM_Mc" <JMMc@discussions.microsoft.com> wrote in message 
news:458EF6A8-51C2-4839-BCB4-1E00D7F672FC@microsoft.com...
> Thank you so much this has solved a question that has been around for 
> quite
> sometime.  Could you answer another formula for me.  I have two cells that 
> I
> need to sum up, one cell has a set value (E), and the second cell is a 
> value
> that is only entered when needed (F).  How do I sum up the two only when F
> has a value in it?  (If there is no value in (F) I want the formula to not
> place (E)'s value as the sum).
>
>
>
> "Sandy Mann" wrote:
>
>> Tty:
>>
>> =IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))
>>
>> -- 
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> sandymann2@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message
>> news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
>> >I am using an IF formula to calculate between two cells, one is J 
>> >(width)
>> >the
>> > other is K (length).  Currently these formulas give an answer that then
>> > has
>> > to be rounded up based on the decimal place.  I need the formula to 
>> > also
>> > round up the amount to quarter increments.  For example if the answer 
>> > is
>> > 1.17
>> > then the formula needs to make it 1.25, if it is 1.33 then the formula
>> > needs
>> > to make it 1.5, and finally if it is 1.63 then the formula needs to 
>> > make
>> > it
>> > 1.75.  So how do I add or make the formula round up to quarter 
>> > increments?
>> > The formula that I am using is:
>> > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))
>> >
>>
>>
>>
> 


0
sandymann2 (1054)
5/23/2008 7:10:19 PM
Here's another option for your first question.....

=CEILING(K41/INT(7-J41),0.25)

"Sandy Mann" wrote:

> Try:
> 
> =IF(F41="","",SUM(E41:F41))
> 
> -- 
> HTH
> 
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
> 
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
> 
> 
> "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message 
> news:458EF6A8-51C2-4839-BCB4-1E00D7F672FC@microsoft.com...
> > Thank you so much this has solved a question that has been around for 
> > quite
> > sometime.  Could you answer another formula for me.  I have two cells that 
> > I
> > need to sum up, one cell has a set value (E), and the second cell is a 
> > value
> > that is only entered when needed (F).  How do I sum up the two only when F
> > has a value in it?  (If there is no value in (F) I want the formula to not
> > place (E)'s value as the sum).
> >
> >
> >
> > "Sandy Mann" wrote:
> >
> >> Tty:
> >>
> >> =IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))
> >>
> >> -- 
> >> HTH
> >>
> >> Sandy
> >> In Perth, the ancient capital of Scotland
> >> and the crowning place of kings
> >>
> >> sandymann2@mailinator.com
> >> Replace @mailinator.com with @tiscali.co.uk
> >>
> >>
> >> "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message
> >> news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
> >> >I am using an IF formula to calculate between two cells, one is J 
> >> >(width)
> >> >the
> >> > other is K (length).  Currently these formulas give an answer that then
> >> > has
> >> > to be rounded up based on the decimal place.  I need the formula to 
> >> > also
> >> > round up the amount to quarter increments.  For example if the answer 
> >> > is
> >> > 1.17
> >> > then the formula needs to make it 1.25, if it is 1.33 then the formula
> >> > needs
> >> > to make it 1.5, and finally if it is 1.63 then the formula needs to 
> >> > make
> >> > it
> >> > 1.75.  So how do I add or make the formula round up to quarter 
> >> > increments?
> >> > The formula that I am using is:
> >> > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))
> >> >
> >>
> >>
> >>
> > 
> 
> 
> 
0
5/24/2008 1:48:01 PM
Sorry, that would need to be....

=CEILING(K41/MIN(4,MAX(2,INT(7-J41))),0.25)



"daddylonglegs" wrote:

> Here's another option for your first question.....
> 
> =CEILING(K41/INT(7-J41),0.25)
> 
> "Sandy Mann" wrote:
> 
> > Try:
> > 
> > =IF(F41="","",SUM(E41:F41))
> > 
> > -- 
> > HTH
> > 
> > Sandy
> > In Perth, the ancient capital of Scotland
> > and the crowning place of kings
> > 
> > sandymann2@mailinator.com
> > Replace @mailinator.com with @tiscali.co.uk
> > 
> > 
> > "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message 
> > news:458EF6A8-51C2-4839-BCB4-1E00D7F672FC@microsoft.com...
> > > Thank you so much this has solved a question that has been around for 
> > > quite
> > > sometime.  Could you answer another formula for me.  I have two cells that 
> > > I
> > > need to sum up, one cell has a set value (E), and the second cell is a 
> > > value
> > > that is only entered when needed (F).  How do I sum up the two only when F
> > > has a value in it?  (If there is no value in (F) I want the formula to not
> > > place (E)'s value as the sum).
> > >
> > >
> > >
> > > "Sandy Mann" wrote:
> > >
> > >> Tty:
> > >>
> > >> =IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))
> > >>
> > >> -- 
> > >> HTH
> > >>
> > >> Sandy
> > >> In Perth, the ancient capital of Scotland
> > >> and the crowning place of kings
> > >>
> > >> sandymann2@mailinator.com
> > >> Replace @mailinator.com with @tiscali.co.uk
> > >>
> > >>
> > >> "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message
> > >> news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
> > >> >I am using an IF formula to calculate between two cells, one is J 
> > >> >(width)
> > >> >the
> > >> > other is K (length).  Currently these formulas give an answer that then
> > >> > has
> > >> > to be rounded up based on the decimal place.  I need the formula to 
> > >> > also
> > >> > round up the amount to quarter increments.  For example if the answer 
> > >> > is
> > >> > 1.17
> > >> > then the formula needs to make it 1.25, if it is 1.33 then the formula
> > >> > needs
> > >> > to make it 1.5, and finally if it is 1.63 then the formula needs to 
> > >> > make
> > >> > it
> > >> > 1.75.  So how do I add or make the formula round up to quarter 
> > >> > increments?
> > >> > The formula that I am using is:
> > >> > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))
> > >> >
> > >>
> > >>
> > >>
> > > 
> > 
> > 
> > 
0
5/24/2008 1:54:03 PM
Hi Sandy,

You might want to simplify the formula below to read:

=CEILING(K1/IF(J1<=3,4,IF(J1<=4,3,IF(J1>4,2))),0.25)

Cheers,
Shane Devenshire
Microsoft Excel MVP

"Sandy Mann" <sandymann2@mailinator.com> wrote in message 
news:eivwzHQvIHA.4772@TK2MSFTNGP03.phx.gbl...
> Tty:
>
> =IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))
>
> -- 
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message 
> news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
>>I am using an IF formula to calculate between two cells, one is J (width) 
>>the
>> other is K (length).  Currently these formulas give an answer that then 
>> has
>> to be rounded up based on the decimal place.  I need the formula to also
>> round up the amount to quarter increments.  For example if the answer is 
>> 1.17
>> then the formula needs to make it 1.25, if it is 1.33 then the formula 
>> needs
>> to make it 1.5, and finally if it is 1.63 then the formula needs to make 
>> it
>> 1.75.  So how do I add or make the formula round up to quarter 
>> increments?
>> The formula that I am using is:
>> =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))
>>
>
> 
0
5/24/2008 8:46:54 PM
Hi again,

I see you got a shorter formula already, that one can also be shortened to:

=CEILING(K1/MIN(4,MAX(2,7-J1)),0.25)

Cheers,
Shane Devenshire
Microsoft Excel MVP

"JM_Mc" <JMMc@discussions.microsoft.com> wrote in message 
news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
> I am using an IF formula to calculate between two cells, one is J (width) 
> the
> other is K (length).  Currently these formulas give an answer that then 
> has
> to be rounded up based on the decimal place.  I need the formula to also
> round up the amount to quarter increments.  For example if the answer is 
> 1.17
> then the formula needs to make it 1.25, if it is 1.33 then the formula 
> needs
> to make it 1.5, and finally if it is 1.63 then the formula needs to make 
> it
> 1.75.  So how do I add or make the formula round up to quarter increments?
> The formula that I am using is:
> =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2))) 

0
5/24/2008 8:53:31 PM
Reply:

Similar Artilces:

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

a simple math formula
Hi, I'm new to this and have a very simple question. I have values, simple numbers representing weight in kg, that I wish to automatically convert to US pounds. The 'kg' numbers are in fields B5 - to B77 for example. I want the conversion result (simply multiplying each B cell entry by 2.2) placed in the ''cell adjacent. Thanks! Dave Horne Hi David In C5 enter =CONVERT(B5,"kg","lbm") and copy down through C6:C77 -- Regards Roger Govier "Dave Horne" <davehorne@home.nl> wrote in message news:upOf6pgUJHA.4916@TK2MSFTNGP06.p...

Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is there a way to abbreviate this value so the other data points show better in the graph? Hi, One way is to break the Y axis, have a look at these examples of how to http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy CMEknit wrote: > I've got a chart where one value (8,300) greatly exceeds all the others. Is > there a way to abbreviate this value so the other da...

Array Formula #7
I have an array formula that works correct up to the 20th row. It quit running on row 20 or it does not pick up any information after row 20. Any help out ther -- pete576 ----------------------------------------------------------------------- pete5761's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2961 View this thread: http://www.excelforum.com/showthread.php?threadid=49427 Rather impossible to tell... What's the formula, and what cell(s) is it entered into? What type of data is in any referenced ranges? In article <pete5761.205n5a_1134773401.141...

Labels in Formulas requires manual refresh?
I use Labels in Formulas extensively. (Excel 2003 11.5612.5606) That is, I tick off Options/Calculations/"Accept Labels in Formulas" and then use whatever text string I've placed at the top of a column of data to refer to it. It has many benefits over Named Ranges, dynamic or otherwise for my purposes, but one ongoing annoyance. When I add data to the bottom of the column of data, the in-cell formula using the label for reference does not change when it should. Workaround: Select cell, F2 or double click to open it, Enter. I have a number of such formulae so updating can be ...

Scheduling formula question
I know both are the same equation. Which one is by definition ? "Duration = Work / Units" or "Work = Duration x Units". I have a three day task with a resource assigned (Max. Units 100%, Units:100%). All calendars are the default Standard base calendar; Hours per day is 9 hours. How do we build the equation to calculate 27 hours of work ? TBol -- To be technically correct, the Duration Equation formula is written as: Duration = Work/(Hours Per Day x Units) You find the Hours Per Day value on the Calendar page of the Options dialog, accessed by clic...

Return values that sum to a known value
I have a list of data and would like to know if there is a formula that would return any items from that list that sum to a known value. Have a look at this thread for something similar: http://www.microsoft.com/office/community/en-us/default.mspx?pg=7&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&fltr= Regards, Tom "lmattern" wrote: > I have a list of data and would like to know if there is a formula that would > return any items from that list that sum to a known value. ...

Need help on a formula 05-20-10
I need a formula to calculate the following information please: I will have someone enter a time (ex 6:31) in cell C3. If the time matches one of the times in cells B17-B22 I need it to display 4.6, if it matched one of the times in cells B23-28 I need it to display 4.7, if it matches one of the times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a simple formula I can do for this please? =IF(COUNTIF(B17:B22,C3)>0,4.6,IF(COUNTIF(B23:B28,C3)>0,4.7,IF(COUNTIF(B29:B34,C3)>0,4.8,"no match"))) Can't do the "and so on" part, bu...

How do I import data from lotus123 & maintain formulas/worksheets
I am trying to convert several complex Lotus 123 workbooks with formulas into Excel 2003. How do I do this and maintain my formulas and the individual tabs (worksheets). hi, if the lotus file is a wks version or earlier, xl should open it and let you save it as an xl file. if the lotus file is a 123 version or higher, you can open the file in lotus and save it as an xl file. if you don't have lotus, find someone who does. "Ineluctable" wrote: > I am trying to convert several complex Lotus 123 workbooks with formulas into > Excel 2003. How do I do this and maintai...

Delete contents deletes all data and formulas
When I hit delete contents all data and formulas are deleted. How can I delete data without deleting formulas? Hi, You could try this tap F5 - Special - Constants - OK and if that selects the data you want to delete then tap the delete key -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tdhcrr" wrote: > When I hit delete contents all data and formulas are deleted. How can I > delete data without deleting formulas? First use Find...

Excel Formula Help
I am setting up a basic excel spreadsheet and really have got no experience with excel. I want to do something real basic like if A2=laptop display $10 or if A2=desktop display $20 how do I do this? I think that if the list of options is more than a just a few, a =vlookup() function would work nicely. It may seem a little complex to start, but once you use it, you'll find tons of more reasons to use it. Debra Dalgleish has some nice instructions at: http://contextures.com/xlFunctions02.html BadSector wrote: > > I am setting up a basic excel spreadsheet and really have got no...

Formula to find last monday (tue, wedn, thu or friday) for a given month
Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit http://www.cpearson.com/excel/DateTimeWS.htm#DaysInMonth -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <mw001@pandora.be> wrote in message news:b30b6913.0402090708.556d0faa@posting.google.com... > Hi, > I need a...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

How do I make X-values of a chart dependent on values in cells?
Greetings. I have a chart which can go from x-value 0 to x-value 200. However I'd like to be able to input min X-value into a cell, and a max X-valu into a cell, and the x-value in the chart changes to reflect that. Is it possible to do that? Thanks for any replies. K -- Message posted from http://www.ExcelForum.com Hi, There is no automatic way to do this but take a look a Tushar's AutoChart Manager for a possible solution. (http://www.tushar-mehta.com/) Cheers Andy Kashgarinn < wrote: > Greetings. > > I have a chart which can go from x-value 0 to x-value 200...

creating nested formulas from drop down box
Hi, I have cell A1 with a drop down box containing 26 available choices. B1 has the dollar amount matching to the choice in A1 using vlookup. E1 totals several cells including B1 together. I want F1 to look at A1 and either enter the number from E1 or NA. Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 (total of b-d1) F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="...

I need to round up to the nearest 50.
I am trying to calculate the number of raw material bags I need in a batch. If I use the mround function is will round down. Is there a way to only round up? =ROUNDUP(A21/50,0)*50 or =CEILING(A21,50) -- HTH Bob Phillips "karenm" <karenm@discussions.microsoft.com> wrote in message news:DBE54F5E-0524-4DFF-BC52-D966BBEE26E9@microsoft.com... > I am trying to calculate the number of raw material bags I need in a batch. > If I use the mround function is will round down. Is there a way to only > round up? > Hi karenm, First of all, I believe MROUND rounds...

set value of a group of activex control points
Have a spreadsheet that has some 20+ activex control points (option buttons). Is there a way to group all these controls together & set their initial values the same? Trying to setup a "reset" type of operation that would clear all control points. I can do them individually via properties, but it's too time consuming. Any suggestions? ...

passeord Protect for excel formula and VBA code
Hi, i have excel sheet its contain lot of formula and VBA(macro) code, its for used for user purpose, i need how to protect the formula and VBA at the time of user using. kindly let me know , kindly help me out i need user password : for user can upload the data in non restriction cell, admin password : can change any thing(change power) pls help me, Please ask on the Excel board. "deen" wrote: > Hi, > > i have excel sheet its contain lot of formula and VBA(macro) code, > > its for used for user purpose, > > i need how to protect the formula and ...

Storing distinct values in an array
Hello .. in the speadsheet, i have a column containing a series of numbers maybe of them repeated multiple times... i would like to store all distinct values in an array any ideas on how i could do that ... eg , if these were the numbers going down column then 1 3 5 7 5 3 5 7 5 4 3 4 5 7 8 5 3 .. store 1,3,5,7,9,4,8 in an array, in no specifi order thank you shimee -- shimee ----------------------------------------------------------------------- shimeel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1583 View this thread: http://www.excelforum.com/showt...

Tiebreaker in a Index formula?
I need a tiebreaker for a formula. Right now the formula reads: =INDEX(A$3:A$21,MATCH(D28,Y$3:Y$21,0)) which works real well But when there is a tie it shows on one store twice instead of listing the two separate stores. I have two stores have the exact scores and it list the store with the lower store number twice. For example I have store 598 and store 698 both with a score of 100% but in the ranking of the stores it shows store 598 twice instead of 598 then 698. Is it possible to have a tiebreaker, with the index formula, that can list the stores in descending order, 598 first...

Populate cell with a value if another cell has a certain value
All, good morning. i have a issue, i need to populate cell E4 with a value CX/025966 when cell F4 has a text Bond Street. Is there a simple way of doin this? EXCEL 2007 Try:- =IF((F4="Bond Street"),"CX/025966","") If my comments have helped please hit Yes. Thanks. "B2ORL" wrote: > All, good morning. i have a issue, i need to populate cell E4 with a value > CX/025966 when cell F4 has a text Bond Street. > > Is there a simple way of doin this? Use IF() =IF(F4="Bond Street","CX/02...

locking / unlocking cell in formula
E F 1 Do you own a guitar validation cell (yes/no) 2 If yes is it a Gibson 3 Is it a 5 string 4 Is it electric 5 Do you own a piano 6 Is it electric 7 Does it have 88 keys 8 Do you own a TV 9 Is it color 10 Is it an LCD 11 Is it 25" 12 Is it 32" 13 Is it 42" 14 Do you own a radio I would like to lock cells F2:F4 if F1 contains the word "no" I would also like to lock cells F6:F7 if F5 contains the word "no" I would also like to lock ce...

plz help in to creat formula of excel
hello to all i want to discuss my problem with you related to MS excel. i want to know or find out formula of excel which can help me in such way.... i want to make grade point of my college students in such a way.. for example.... marks grade point average 50 1 53 1.3 60 2 68 2.8 70 3 76 3.6 80 4 84 4 89 4 90 4 98 4 100 4 this is example the student who get 50 marks the GPA will be 1 and who get 80 marks or above 80 the GPA should be 4 GPA should not less than 1 or grator than 4 please tell me the formula so that i can make this GPA thanks a lo -- khushe --------------------------...

Show zero values
When a formula returns a zero value, the zero value is not being displayed. I have reviewed the following: - conditional formatting - zero values are checked (Tools/Options/View) - stepped through my VB code (it inserts formulae based on a Worksheet Change event) - locked and hidden values unchecked with and without protection (this should have no effect when the sheet is unprotected) Are there any other circumstances that result in a zero value not being displayed? Are there any other reasons for this occurrence? Cheers John Check also normal cell formatting! there are formats that hide ...

display result of formula
How do I make a cell which contains a formula display the result of that calculation rather than the formula (it is formatted as "number" and I have tried "recalculating") Peter Peter Chadbund expressed precisely : > How do I make a cell which contains a formula display the result of that > calculation rather than the formula (it is formatted as "number" and I have > tried "recalculating") > Peter Change the cell format to 'General'. Redo the formula. Change the cell format to 'Number' and apply your display preferenc...