Formula to process 3 cells using IF statements

I have 3 columns of experimental data (C:E).

Row 30 contains the sums (C30:E30).

I need a formula that will examine the three sums and return the
column number that has the lowest sum. If more than one column is
lowest, select one randomly.

Example:

  C30  D30  E30  Result
   10   11   12  1 (C)
   22   20   21  2 (D)
   32   31   30  3 (E)
   40   41   40  Randomly select 1 or 3
   51   50   50  Randomly select 2 or 3
   60   60   60  Randonly select 1, 2, or 3

Can this be done with IF statements or do I need to write a macro?
0
Prof
12/18/2009 4:49:00 PM
excel 39879 articles. 2 followers. Follow

6 Replies
720 Views

Similar Articles

[PageSpeed] 14

Well, this is a bit cumbersome, but it seems to do the job:

=3DCHAR(IF(COUNTIF(C30:E30,MIN(C30:E30))=3D1,MATCH(MIN
(C30:E30),C30:E30,0),IF(COUNTIF(C30:E30,MIN(C30:E30))=3D3,INT(RAND()
*3)+1,--MID(SUBSTITUTE("123",MATCH(MAX(C30:E30),C30:E30,0),""),INT(RAND
()*2)+1,1)))+66)

It returns the column letter chosen according to your rules.

Hope this helps.

Pete

On Dec 18, 4:49=A0pm, Prof Wonmug <won...@e.mcc> wrote:
> I have 3 columns of experimental data (C:E).
>
> Row 30 contains the sums (C30:E30).
>
> I need a formula that will examine the three sums and return the
> column number that has the lowest sum. If more than one column is
> lowest, select one randomly.
>
> Example:
>
> =A0 C30 =A0D30 =A0E30 =A0Result
> =A0 =A010 =A0 11 =A0 12 =A01 (C)
> =A0 =A022 =A0 20 =A0 21 =A02 (D)
> =A0 =A032 =A0 31 =A0 30 =A03 (E)
> =A0 =A040 =A0 41 =A0 40 =A0Randomly select 1 or 3
> =A0 =A051 =A0 50 =A0 50 =A0Randomly select 2 or 3
> =A0 =A060 =A0 60 =A0 60 =A0Randonly select 1, 2, or 3
>
> Can this be done with IF statements or do I need to write a macro?

0
Pete_UK
12/18/2009 5:24:18 PM
On Fri, 18 Dec 2009 09:24:18 -0800 (PST), Pete_UK
<pashurst@auditel.net> wrote:

>Well, this is a bit cumbersome, but it seems to do the job:
>
>=CHAR(IF(COUNTIF(C30:E30,MIN(C30:E30))=1,MATCH(MIN
>(C30:E30),C30:E30,0),IF(COUNTIF(C30:E30,MIN(C30:E30))=3,INT(RAND()
>*3)+1,--MID(SUBSTITUTE("123",MATCH(MAX(C30:E30),C30:E30,0),""),INT(RAND
>()*2)+1,1)))+66)

Amazing. It works. 

I already have a headache trying to decode it.

I moved my data so the three values are now in M30:O30. I first just
changed C30:E30 to M30:O30. That worked but returned C/D/E. Then I
realized that the "66" is the ASCII code for "B" (the letter just
before "C". When I changed that to 76, it worked:

=CHAR(IF(COUNTIF(M36:O36,MIN(M36:O36))=1,MATCH(MIN
(M36:O36),M36:O36,0),IF(COUNTIF(M36:O36,MIN(M36:O36))=3,INT(RAND()
*3)+1,--MID(SUBSTITUTE("123",MATCH(MAX(M36:O36),M36:O36,0),""),INT(RAND
()*2)+1,1)))+76)

After my headache goes away, I think I try to figure out enough of
what it is doing so that I can have it return an index (1/2/3).

Then, I'll write a macro, which is what I should have done in the
first place.

I owe you a beer for clever coding.

Have you ever heard of an old IBM programming language called APL?
Hard core APL programmers used to have contests for one-line code
solutions. The more obscure the better. One guy actually wrote a
1-line program to convert Arabic numbers to Roman numerals!

You would have been a great APL programmer. ;-)

>It returns the column letter chosen according to your rules.
>
>Hope this helps.
>
>Pete
>
>On Dec 18, 4:49�pm, Prof Wonmug <won...@e.mcc> wrote:
>> I have 3 columns of experimental data (C:E).
>>
>> Row 30 contains the sums (C30:E30).
>>
>> I need a formula that will examine the three sums and return the
>> column number that has the lowest sum. If more than one column is
>> lowest, select one randomly.
>>
>> Example:
>>
>> � C30 �D30 �E30 �Result
>> � �10 � 11 � 12 �1 (C)
>> � �22 � 20 � 21 �2 (D)
>> � �32 � 31 � 30 �3 (E)
>> � �40 � 41 � 40 �Randomly select 1 or 3
>> � �51 � 50 � 50 �Randomly select 2 or 3
>> � �60 � 60 � 60 �Randonly select 1, 2, or 3
>>
>> Can this be done with IF statements or do I need to write a macro?
0
Prof
12/19/2009 5:18:22 AM
Thanks, Prof - yes, I remember APL, although I never used it as I was
more into Fortran (I suppose both of us are showing our age).

If you want just an index of 1/2/3, then remove the "CHAR(" at the
beginning of the formula, and the "+66)" {or in your case the "+76)" }
from the end of the formula.

Hope this helps.

Pete

On Dec 19, 5:18=A0am, Prof Wonmug <won...@e.mcc> wrote:
> On Fri, 18 Dec 2009 09:24:18 -0800 (PST), Pete_UK
>
> <pashu...@auditel.net> wrote:
> >Well, this is a bit cumbersome, but it seems to do the job:
>
> >=3DCHAR(IF(COUNTIF(C30:E30,MIN(C30:E30))=3D1,MATCH(MIN
> >(C30:E30),C30:E30,0),IF(COUNTIF(C30:E30,MIN(C30:E30))=3D3,INT(RAND()
> >*3)+1,--MID(SUBSTITUTE("123",MATCH(MAX(C30:E30),C30:E30,0),""),INT(RAND
> >()*2)+1,1)))+66)
>
> Amazing. It works.
>
> I already have a headache trying to decode it.
>
> I moved my data so the three values are now in M30:O30. I first just
> changed C30:E30 to M30:O30. That worked but returned C/D/E. Then I
> realized that the "66" is the ASCII code for "B" (the letter just
> before "C". When I changed that to 76, it worked:
>
> =3DCHAR(IF(COUNTIF(M36:O36,MIN(M36:O36))=3D1,MATCH(MIN
> (M36:O36),M36:O36,0),IF(COUNTIF(M36:O36,MIN(M36:O36))=3D3,INT(RAND()
> *3)+1,--MID(SUBSTITUTE("123",MATCH(MAX(M36:O36),M36:O36,0),""),INT(RAND
> ()*2)+1,1)))+76)
>
> After my headache goes away, I think I try to figure out enough of
> what it is doing so that I can have it return an index (1/2/3).
>
> Then, I'll write a macro, which is what I should have done in the
> first place.
>
> I owe you a beer for clever coding.
>
> Have you ever heard of an old IBM programming language called APL?
> Hard core APL programmers used to have contests for one-line code
> solutions. The more obscure the better. One guy actually wrote a
> 1-line program to convert Arabic numbers to Roman numerals!
>
> You would have been a great APL programmer. ;-)
>
>
>
> >It returns the column letter chosen according to your rules.
>
> >Hope this helps.
>
> >Pete
>
> >On Dec 18, 4:49=A0pm, Prof Wonmug <won...@e.mcc> wrote:
> >> I have 3 columns of experimental data (C:E).
>
> >> Row 30 contains the sums (C30:E30).
>
> >> I need a formula that will examine the three sums and return the
> >> column number that has the lowest sum. If more than one column is
> >> lowest, select one randomly.
>
> >> Example:
>
> >> =A0 C30 =A0D30 =A0E30 =A0Result
> >> =A0 =A010 =A0 11 =A0 12 =A01 (C)
> >> =A0 =A022 =A0 20 =A0 21 =A02 (D)
> >> =A0 =A032 =A0 31 =A0 30 =A03 (E)
> >> =A0 =A040 =A0 41 =A0 40 =A0Randomly select 1 or 3
> >> =A0 =A051 =A0 50 =A0 50 =A0Randomly select 2 or 3
> >> =A0 =A060 =A0 60 =A0 60 =A0Randonly select 1, 2, or 3
>
> >> Can this be done with IF statements or do I need to write a macro?- Hi=
de quoted text -
>
> - Show quoted text -

0
Pete_UK
12/19/2009 4:22:27 PM
By the way, if you want a brief explanation of what it does:

The first IF checks if there is only one minimum value and if there is
it returns the index of the column containing the minimum.

The second IF checks if there are 3 equal minima, and if so it returns
a random number in the range 1 - 3

If neither of those IFs are true then there must be 2 minima. The
SUBSTITUTE function finds the index of the maximum value and removes
it from the string "123", so that you have "12", "13", or "23", then
the MID function chooses one of these two characters randomly and the
-- converts it to a number.

The CHAR( ... +66) converts it to a letter, as you have found.

Hope this helps.

Pete

On Dec 19, 5:18=A0am, Prof Wonmug <won...@e.mcc> wrote:
> On Fri, 18 Dec 2009 09:24:18 -0800 (PST), Pete_UK
>
> <pashu...@auditel.net> wrote:
> >Well, this is a bit cumbersome, but it seems to do the job:
>
> >=3DCHAR(IF(COUNTIF(C30:E30,MIN(C30:E30))=3D1,MATCH(MIN
> >(C30:E30),C30:E30,0),IF(COUNTIF(C30:E30,MIN(C30:E30))=3D3,INT(RAND()
> >*3)+1,--MID(SUBSTITUTE("123",MATCH(MAX(C30:E30),C30:E30,0),""),INT(RAND
> >()*2)+1,1)))+66)
>
> Amazing. It works.
>
> I already have a headache trying to decode it.
>
> I moved my data so the three values are now in M30:O30. I first just
> changed C30:E30 to M30:O30. That worked but returned C/D/E. Then I
> realized that the "66" is the ASCII code for "B" (the letter just
> before "C". When I changed that to 76, it worked:
>
> =3DCHAR(IF(COUNTIF(M36:O36,MIN(M36:O36))=3D1,MATCH(MIN
> (M36:O36),M36:O36,0),IF(COUNTIF(M36:O36,MIN(M36:O36))=3D3,INT(RAND()
> *3)+1,--MID(SUBSTITUTE("123",MATCH(MAX(M36:O36),M36:O36,0),""),INT(RAND
> ()*2)+1,1)))+76)
>
> After my headache goes away, I think I try to figure out enough of
> what it is doing so that I can have it return an index (1/2/3).
>
> Then, I'll write a macro, which is what I should have done in the
> first place.
>
> I owe you a beer for clever coding.
>
> Have you ever heard of an old IBM programming language called APL?
> Hard core APL programmers used to have contests for one-line code
> solutions. The more obscure the better. One guy actually wrote a
> 1-line program to convert Arabic numbers to Roman numerals!
>
> You would have been a great APL programmer. ;-)
>
>
>
> >It returns the column letter chosen according to your rules.
>
> >Hope this helps.
>
> >Pete
>
> >On Dec 18, 4:49=A0pm, Prof Wonmug <won...@e.mcc> wrote:
> >> I have 3 columns of experimental data (C:E).
>
> >> Row 30 contains the sums (C30:E30).
>
> >> I need a formula that will examine the three sums and return the
> >> column number that has the lowest sum. If more than one column is
> >> lowest, select one randomly.
>
> >> Example:
>
> >> =A0 C30 =A0D30 =A0E30 =A0Result
> >> =A0 =A010 =A0 11 =A0 12 =A01 (C)
> >> =A0 =A022 =A0 20 =A0 21 =A02 (D)
> >> =A0 =A032 =A0 31 =A0 30 =A03 (E)
> >> =A0 =A040 =A0 41 =A0 40 =A0Randomly select 1 or 3
> >> =A0 =A051 =A0 50 =A0 50 =A0Randomly select 2 or 3
> >> =A0 =A060 =A0 60 =A0 60 =A0Randonly select 1, 2, or 3
>
> >> Can this be done with IF statements or do I need to write a macro?- Hi=
de quoted text -
>
> - Show quoted text -

0
Pete_UK
12/19/2009 4:33:29 PM
On Sat, 19 Dec 2009 08:22:27 -0800 (PST), Pete_UK
<pashurst@auditel.net> wrote:

>Thanks, Prof - yes, I remember APL, although I never used it as I was
>more into Fortran (I suppose both of us are showing our age).

APL is to Fortran as Beethoven is to Barry Manilow. ;-)

>If you want just an index of 1/2/3, then remove the "CHAR(" at the
>beginning of the formula, and the "+66)" {or in your case the "+76)" }
>from the end of the formula.

I ended up writing a macro. That's what I should have done in the
first place, but then I'd have missed out on your impressive one-liner
skills.


Here's the macro, in case anyone cares. It only works on a row of
values, but the range can be any length. 


Public Function MyRandomMinIndex(values As Range)

Dim min, NumCols As Long, mins(), i As Long, NumMins As Integer
NumCols = values.Columns.Count

ReDim mins(1 To NumCols)
'min = MyMin(values)                 'Get the minimum value
min = Application.min(values)

NumMins = 0                         'Min counter
For i = 1 To NumCols                'Find out how many mins we have
  If values.Cells(1, i) = min Then
    NumMins = NumMins + 1
    mins(NumMins) = i
  End If
Next i

i = Int((NumMins * Rnd) + 1)        'Generate random value between 1
and NumMins

MyRandomMinIndex = mins(i)          'Return index of one of the mins



Please feel free to critique.
0
Prof
12/19/2009 11:56:20 PM
Hello,

I have solved your problem also. The results say: 1a or 2b or 3c. (data in 
cols. a,b and c)
 You could enter all your experimental data  in columns a1,b1,c1, all the 
way down to an,bn,cn.
By copying the formula which may be entered in say : cell e1 down to en you 
will get a result as soon a new line of three experimental data  are 
entered.

Best Regards

Gabor Sebo
MA   USA


=CHOOSE(FREQUENCY(A1:C1,MIN(A1:C1)),IF(A1=MIN(A1:C1),"1a",IF(B1=MIN(A1:C1),"2b","3c")),IF(A1<>MIN(A1:C1),CHOOSE(RANDBETWEEN(1,2),"2b","3c"),IF(B1<>MIN(A1:C1),CHOOSE(RANDBETWEEN(1,2),"1a","3c"),IF(C1<>MIN(A1:C1),CHOOSE(RANDBETWEEN(1,2),"1a","2b")))),CHOOSE(RANDBETWEEN(1,3),"1A","2B","3C"))
"Prof Wonmug" <wonmug@e.mcc> wrote in message 
news:44cni5trbvmvena7u90i02esqkg9bss1um@4ax.com...
>I have 3 columns of experimental data (C:E).
>
> Row 30 contains the sums (C30:E30).
>
> I need a formula that will examine the three sums and return the
> column number that has the lowest sum. If more than one column is
> lowest, select one randomly.
>
> Example:
>
>  C30  D30  E30  Result
>   10   11   12  1 (C)
>   22   20   21  2 (D)
>   32   31   30  3 (E)
>   40   41   40  Randomly select 1 or 3
>   51   50   50  Randomly select 2 or 3
>   60   60   60  Randonly select 1, 2, or 3
>
> Can this be done with IF statements or do I need to write a macro?
> 

0
helene
1/30/2010 11:47:02 PM
Reply:

Similar Artilces:

Solving a formula
I have the following formula that I need Excel to solve for different values of P6 ( I have different values of P6 (P7, P8, P9 (reference cells)). I need the value of x for the different values of P6. Is this possible? It seems like it should be but I can't find out how to make Excel do this. Please help! Thanks. 70000 = P6*((((x/80)*(1/5280))*(2/3))+(((x/40)*(1/5280))*(1/3))) If I am not mistaken, x equals: = 22176000000 / P6 HTH -- Dana DeLouis Win XP & Office 2003 "Anth" <Anth@discussions.microsoft.com> wrote in message news:6196C6C5-FC82-42E2-B6A1-B2...

if then else statement
On my report I have a field that I need help with. I have written an =If statement to try and capture all of the variables, but it is too long, so now I need to write the same expression in code. Here is what I have in my =If statement (which resides in the Detail area of the Report): =IIf([costcode]="013210" And [costtype]="05320",[txtActualCost]/[txtCraftLabAct],IIf([costcode]="020110" And [costtype]="05320",[txtActualCost]/[txtFirewatchLabAct],IIf([costcode]="064201" And [costtype]="05320",[txtActualCost]/[txtC...

Use crm?
I would like to know what Microsoft product(s) would be could used for the following results and what the price of the recommended product would be: I work for a small company that would like to keep track of the following: 1. the workload of each employee, 2. how many hours are assigned to each each employee for a project, 3. How many hours each employee have spent on each project, 4. A way to tell if an employee is on track for meeting the project deadlines, 5. A way to tell an employee how many hours they should be working on each project they are assigned to. 6. A way to...

Changing Function to use a Date Input
Hi. can anyone please advise me on how to change the function keys.. sa F10, F11 & F12 to use to input a date into a cell. EG. F10 to be 01/01/04 F11 to be 02/01/04 F12 to be 03/01/04 What I require is to go to any cell and press the relevant function ke to Input the associated date Any help would be greatly appreciate Many thank Rob PS using Excel 200 -- Message posted from http://www.ExcelForum.com First, I'm not sure if you entered Jan 1, 2004, Feb 1, 2004, and Mar 1, 2004 or Jan 1st-3rd, 2004. Here are a couple of macros. The first turns this on, the second turns it...

How can I email an excel file using my outlook contacts?
"cheflady" <cheflady@discussions.microsoft.com> wrote in message news:F9C13E60-150D-4F03-B519-A8769F08CD0F@microsoft.com... > > <sigh> Please use the big white space to write your question........ exactly the same way you email ANY file..... go to file\send\mail recipient( as attachment) "Gordon" wrote: > "cheflady" <cheflady@discussions.microsoft.com> wrote in message > news:F9C13E60-150D-4F03-B519-A8769F08CD0F@microsoft.com... > > > > > > > <sigh> Please use the big white space to write you...

Usage of CRM 3.0
Hi all: Is there any obligation by downloading the beta? I mean, I am willing to download it , but only to play a little bit with it, to understand the internal (SQL) structures, because I have customers with CRM. But nothing else (by now). Any ideas? Thank you for your time (and patience with me) -- Alejandro Leguizamo SQL Server MVP Colombia www.solidqualitylearning.com ___________________________________ Hi Alejandro, You can download MSCRM v3.0 for testing purposes. You cannot use this version for production implementations (key is only valid for 90 days). In case you do fi...

Determine cells that drive conditional formatting?
Example: Cells A1:A4 have conditional formatting set up that states if they are equal to cell A10 they will be highlighted yellow. Is there a way to quickly see what cells drive conditional formatting? In other words, we can use Edit -> Go To Special to see which cells have conditional formatting applied; however, this doesn't show us that A10 is involved. Since there isn't a formula directly involved, we can't trace precedents/dependents. Can we only know that A10 is involved in the formatting of A1:A4 by selecting those cells and going into the conditional format...

Count "," in a cell
Dear expert, Is it possible to count "," in a cell please? I used this ... but does not work =COUNTIF(FO93,",") Say below ... can it be solved? 2,5,3 22,25,5 5,2,3,4 Try this… =LEN(FO93)-LEN(SUBSTITUTE(FO93,",","")) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Elton Law" wrote: > Dear expert, > > Is it possible to count "," in a cell please? > > I used this ... but does not work > > =COUNTIF(FO93,",&qu...

tender using outside gift card
Hi, does anyone know how to 'pass' the transaction total to an outside exe or dll? we are trying to process the transaction with a gift card (not an internal gift card, this GC is another company who wants to 'share' the gift card balance). basically RMS needs to pass the transaction total to the exe (or dll) which will query the balance of the gift card (over the internet and/or local DB). the exe/dll will query its database and return a yes or no to RMS any help would be appreciated. scott@kanesvending.com -- Walt You use what's called a "Hook" function t...

Transferring data between worksheets using Sheet Command?
Hi for an assignment i have to enter grades for 200 students in 4 subjects. there is a front summary sheet that contains all the subjects and all the students and their overall grade GPA etc.. anyway this summary sheet has to be populated automatically from the individual math, english etc.. worksheets. The guide says to do it using the 'sheet command' any help greatly appreciated. Thanks ...

1 Cell 2 Values?
Hello All, Is it possible in excel to have it return two values in one cell separated by a comma and spaces? For example I want it to count the number of times in a month the value was positive and the number of times a value was negative and in the cell I want it to show A1: +, - Is this possible? Thanks to all, Shhhh Shhhh wrote... >Is it possible in excel to have it return two values in one cell separated >by a comma and spaces? > >For example I want it to count the number of times in a month the value was >positive and the number of times a value was negative a...

text to fit in cell
How do I make the text fit in a cell. I want to be able to print the page with all of the words in the cell even if it has to make the cell larger, without going over into the next cell. How do I do this? Hi goto 'Format - Cells - alignment' and check 'Wrap text' -- Regards Frank Kabel Frankfurt, Germany DaveB wrote: > How do I make the text fit in a cell. I want to be able > to print the page with all of the words in the cell even > if it has to make the cell larger, without going over > into the next cell. How do I do this? Dave Format>Cells>Alignm...

Totaling cells from separate worksheets to master form
If I have 2 separate worksheets with individual cell values and want the total to show the sum of obth cells, how do I do it? Nelson Suppose those values are in F10 on one sheet and in G6 of the other sheet, then put this in the appropriate cell of your master sheet: =3DSheet1!F10 + Sheet2!G6 to add them both together. If your actual sheet names contain spaces you will need to include apostophes around the sheet name, like: =3D'First Sheet'!F10 + 'Second Sheet'!G6 Hope this helps. Pete On Apr 21, 10:31=A0pm, snake941 <snake...@discussions.micros...

FOrmula to work out pricing rounding up and down
I have a formula in cell that reads "=3DIF(C7>0,"FOC",-C7*1.175)" it turns a negative into a Positive and a Positive FIgure into answer of "FOC" Sometimes it will display a result of =A32 or =A35.50 or whatever the figure when the original answer is a negative. With me so far!! The result which is displayed as a number i need it to round up t the nearest 9.99. For example if c7 is =A34.00 display answer of 9.99 if c7 is =A311.00 display answer of 19.99 if c7 is =A328.43 display answer of 29.99 So no matter what the answer is it rounds up to the 9.99 answer...

Global variable #3
Visual C++ 6.0 Is it possible to use the declaration "BYTE* buf = new BYTE[8000000]" in the ..h file for global use by multiple routines? If so, how do I clear the array to load different data into it? Thanks Ed wrote: > Visual C++ 6.0 > > Is it possible to use the declaration "BYTE* buf = new BYTE[8000000]" in the > .h file for global use by multiple routines? > If so, how do I clear the array to load different data into it? > Thanks How to create a global: // h file extern BYTE* g_buf; // in a function in one cpp file g_buf = new BYTE[8000000];...

Dumb question... how to create a Enterprise Formula Custom Field
Guys, I'm writing because I've tried and search but can't get it ... From PWA I created a Project Custom Field "MyField" of type Text with the formula "Hello World", I tried with " and '. When I open a project the field returns #ERROR. I published to see if magic happens, but nothing. I did the same with type number and formula 10, same result. So In the end, I can't get any formula to work. What am I doing wrong ... I googled with no success. Perhaps try opening the project and hitting the F9 button to refresh calculations? Does ...

Help me with formula
I have a basic graph 1,2,3,4,5.....going down in and a,b,c,d,e,f,c going across. EX. A B C D E F G ---------------------------------------------- 1 500 25 75 800 (200) 2 3 4 5 6 7 My question is how can I make an equation that Goes like this : Column A+B+D-E=G I showed an example above for on row but i wouuld wnat this to work for any row no matter what data I answer. I want this ewuation to work for any data entered for the whole sheet. I hope you understand and can help anyone. -- thatsall ------...

Christmas lights macro #3
Thanks for that. I'm a complete XL dummy. Can you please explain in 2 sentences how get that into the spreadsheet and run it -- pmolse ----------------------------------------------------------------------- pmolsen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1511 View this thread: http://www.excelforum.com/showthread.php?threadid=26744 Hi pmolsen, See David McRitchies notes at: http://www.mvps.org/dmcritchie/excel/getstarted.htm --- Regards, Norman "pmolsen" <pmolsen.1dsg3z@excelforum-nospam.com> wrote in message news:pmolsen....

Format Excel cells
Anyone could help me to solve this problem : How to format the selected cells so that it allows other users to "paste values" only(number from 0 to 100) to the cells. It rejects "paste" function which put formula, format etc into the cells. Thanks a million ! ---------------- 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 Newsreade...

cells changing from number to date
I am putting a number in a cell, then tabbing to the next cell, the previous cell changed from a number to a date. How do I keep the number there? What are you inputting, a fraction?if so, format as # ??/?? -- HTH RP (remove nothere from the email address if mailing direct) "mwhite@srgmac.com" <mwhite@srgmac.com@discussions.microsoft.com> wrote in message news:E71CED6D-8370-44D1-A4BF-9A6B352205F3@microsoft.com... > I am putting a number in a cell, then tabbing to the next cell, the previous > cell changed from a number to a date. How do I keep the number there? ...

formula = ADD whatever is not highlighted.
Hi. I have a workbook which I use as my monthly budget. I have tried other software made for that, but this is really what I like. As transactions go through, I "highlight" the cell, using a variety of colors. I would like 1 cell (presumably, one that would calculate, what is left to go through) to add only the cells that are not highlighted, in a certain range. Any and all help is greatly appreciated. These should help. Sub addnoncolor() mysum = 0 For Each c In [m1:m5] If c.Interior.ColorIndex = 2 Then mysum = mysum + 1 Next MsgBox mysum End Sub Sub whatcolor() For Eac...

How to use this DeleteEMFs() Macro
This macro was on the MicroSoft website as a work around for the .emf storage problem http://support.microsoft.com/default.aspx?scid=kb;en-us;299372 What exactly does this macro do and how should I use it? I want to remove all the .emf files from a particular Excel File. B/c the workbook creates hundreds or thousands of temp .emf files everytime it opens and it takes forever to open.. Thank you very much for any help! george Private Sub Workbook_Open() Call DeleteEMFs End Sub Private Sub DeleteEMFs() Dim fso As Variant Set fso = CreateObject("Scripting.FileSystemObject"...

CALCULATING DEPRECIATION USING ACCESS
HOW DO I DO IT? Why don't you ask the Access newsgroup instead of the Microsft CRM ng? -- Brandon IT Director Office Equipment & Supplies at http://www.presentationsdirect.com "PATRICK" <PATRICK@GLWHOLESALE.COM> wrote in message news:026d01c352d1$755a23b0$a101280a@phx.gbl... > HOW DO I DO IT? ...

Problem
i all I am having a strange issue with my excell file since a while. This is the second year I am using it without problem. I start a workday with a new sheet so every morning I just copy (Edit/Move or Copy Sheet) the latest sheet, rename it and use it. All of a suddent when I perform this operation I have a prompt that says "a formula or sheet you want to move or copy contains the name 'aaa', which exists in the destination worksheet. Do you want to use this version of the name?" I would hit yes to proceed, then I would get over 50 other prompts with differ...

Using 'Add to Favourites' option on a shared calendar not working
In Outlook 2007 we have a few users who are unable to add a shared calendar to their Calendars->Other Calendars list. The steps that work on a mojority of computers are: 1. Go->Folder List. 2. Navigate to Public Folders->All Public Folders->...->Communal Calendar. 3. Right Click on Communal Calendar and select 'Add to Favourites'. 4. Click OK on 'Add to Favourites' dialog. 5. Go->Calendar and the calendar should have appeared under 'Other Calendars' list. However, in a few cases when OK is clicked in step 4 the calendar never appears in...