Formula

  • Thread starter Thread starter Sby
  • Start date Start date
S

Sby

Hi

I am in need of a formula that will do the following.
Input a number if "a" happens then add 3 to g10, if "b"
happens add 1 to g10, and if "c" happens 0.

Can anyone help

Thanks
 
Hi

You can't have a formula and a value in a cell. You'll need to put this
formula in another cell and make G10 be equal to whatever is in G10 and the
value in this new cell.
Eg. in G10 is the number 8. In our new cell (say, G99) we will put the
formula, and you need to change G10 to be
=8+G99
The formula for G99 is:
=IF(a,3,IF(b,1,IF(c,0)))
It is not really necessary for the 'c' option, because the cell will be 0
anyway. This would be:
=IF(a,2,IF(b,1))

Andy.
 
Hi Sby!

Unless I'm understanding you wrong, you can't do this without VBA.

The details of capabilities of functions and formulas are based on:



170787 XL: Custom Functions Can't Change Microsoft Excel Environment

http://support.microsoft.com/default.aspx?scid=kb;en-us;170787



A Function or User Defined Function called by a formula in a worksheet
cell cannot change the environment of Microsoft Excel. This means that
such a function cannot do any of the following:



Ø Insert, delete, or format cells on the spreadsheet.

Ø Change another cell's value.

Ø Move, rename, delete, or add sheets to a workbook.

Ø Change any of the environment options, such as calculation
mode or screen views.

Ø Add names to a workbook.

Ø Set properties or execute most methods.



The purpose of user-defined functions is to allow the user to create a
custom function that is not included in the functions that ship with
Microsoft Excel. These functions, as with built-in functions also
cannot change the environment.



Functions can perform a calculation that returns either a value or
text to the cell that they are entered in. Any environmental changes
should be made either directly or through the use of a Visual Basic
subroutine.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I don't know what you have in G10 to begin with or if these
events can happen repeatedly.

G10: =G9+IF(C10,0,IF(B10,3,0)) --or better as--
=OFFSET(G10,-1,0)+IF(C10,0,IF(B10,3,0))

If these are events that can happen repeatedly from
entering something into other cells then you can use
an Event macro. But you are unnecessarily vague about
what these events are to supply specific coding. Take
a look at http://www.mvps.org/dmcritchie/excel/event.htm
 
Back
Top