Excel Function Problem

[saundej]

Hi,

Please consider me an excel newbie!

I am trying to find out how to write a function that checks a 7 digit
numerical string entered into cell i.e. 7030000 against 50 possible
strings. (Im guessing this is a multiple IF statement thing).

Once the condition is true it then takes the value from cell J7, adds
it to the value in cell D7.

The end result being that D7 now contains a value equal to its original
value + J7.

Any help given would be appreciatted

Many Thanks

 

[Jacson]

You might look at the SumIF function.
It will add values together if they (or related cells)
meet deired criteria.

 

[Mark Graesser]

saundej
If you want to overwrite D7 with a new value you are going to have to do it with programming (Visual Basic). Excel function can return a value but not overwrite an existing valve

In regard to checking the 7 digit string, you best bet would be to use a MATCH or LOOKUP function. Excel has a limit of 7 nested IF statments. You can get around this by concatenating instead of nesting, but I wouldn't suggest concatenating 50 IF statements. You can use one IF statement to check if the MATCH function returns an error (ISNA function). If this is true, then the number is not in the list

example
=IF(ISNA(MATCH(D7,F1:F9,0)),"Not in list","In List"

F1:F9 would be changed to the reference of the list of numbers you want to match

Good Luck
Mark Graesse
(e-mail address removed)

----- saundej > wrote: ----

Hi

Please consider me an excel newbie

I am trying to find out how to write a function that checks a 7 digi
numerical string entered into cell i.e. 7030000 against 50 possibl
strings. (Im guessing this is a multiple IF statement thing)

Once the condition is true it then takes the value from cell J7, add
it to the value in cell D7

The end result being that D7 now contains a value equal to its origina
value + J7

Any help given would be appreciatte

Many Thank

 

[Paul]

Hi,

Please consider me an excel newbie!

I am trying to find out how to write a function that checks a 7 digit
numerical string entered into cell i.e. 7030000 against 50 possible
strings. (Im guessing this is a multiple IF statement thing).

Once the condition is true it then takes the value from cell J7, adds
it to the value in cell D7.

The end result being that D7 now contains a value equal to its original
value + J7.

Any help given would be appreciatted

Many Thanks

D7 can contain either a value or a formula but not both. No formula can add
to itself, as that would be a circular reference. To do what you describe
would need VBA code; it cannot be done with any kind of function.

 

[Bob Phillips]

Not sure where D7 and J7 come into, but it seems you want the MATCH function

=MATCH(7_digit_Num, table_of_50_nums,0)

will return the index where the number resides within the table. You can
check that it exists by

=IF(ISERROR(=MATCH(7_digit_Num, table_of_50_nums,0)),"",D7*J7)

One other thing, you can't put this in D7 as you suggest, as putting the
formula in there will overwrite the value in D7. You need to put it in
another cell, or use VBA.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)