Complicated adding
 

I have an array of numbers and am trying to see if the Mod of two of these
number is less than 1. I have a long list so making a nested if function
would be my last option.

Example

a b wishfull results
1 1.20 combine
2 1.00
3 2.00
4 1.50 combine
5 2.90

Thanks in advance.

Hi
and what is your expected 'combined' result in column C?

--
Regards
Frank Kabel
Frankfurt, Germany
"James W." schrieb im Newsbeitrag
...
I have an array of numbers and am trying to see if the Mod of two of these
number is less than 1. I have a long list so making a nested if function
would be my last option.

Example

a b wishfull results
1 1.20 combine
2 1.00
3 2.00
4 1.50 combine
5 2.90

Thanks in advance.

The mod of the two numbers? Are you referring to the remainder when these
two numbers are divded by or into eachother? If so I'm not sure how you're
arriving at what is "combined" or not..

Have you tried the MOD function? If you have tried that and can see the
results it produces, an IF formula wrapped around the MOD part will give you
"combine" or ""; e.g. =IF(your formula<1,"combine","")




"James W." wrote in message
...
I have an array of numbers and am trying to see if the Mod of two of these
number is less than 1. I have a long list so making a nested if function
would be my last option.

Example

a b wishfull results
1 1.20 combine
2 1.00
3 2.00
4 1.50 combine
5 2.90

Thanks in advance.

I apoligize for not being more specific...
I am using MOD ( a1, 1). I would like an if formula that would tell me
that I can combine these two values. These values represent pallets, so just
simply stating "combine" will sufice.

For instance

if(mod(b1,1)=0,"",if(mod(b1 + **B:B**)<1,"Combine","") b:b
obviously b:b won't work becuase it is taking all of the values, I would
just like it to tell me that it can be combined with one other value
a b wishfull results
1 1.20 "combine" if it could say "combine with 'b4" that would be
better
2 1.00
3 2.00
4 1.50 "combine" if it could say "combine with 'b1" that would be
better
5 2.90


"Dave R." wrote:

The mod of the two numbers? Are you referring to the remainder when these
two numbers are divded by or into eachother? If so I'm not sure how you're
arriving at what is "combined" or not..

Have you tried the MOD function? If you have tried that and can see the
results it produces, an IF formula wrapped around the MOD part will give you
"combine" or ""; e.g. =IF(your formula<1,"combine","")




"James W." wrote in message
...
I have an array of numbers and am trying to see if the Mod of two of these
number is less than 1. I have a long list so making a nested if function
would be my last option.

Example

a b wishfull results
1 1.20 combine
2 1.00
3 2.00
4 1.50 combine
5 2.90

Thanks in advance.

Yes that is a bit complicated. Try something like this. . I tested it with
your sample range and it seems to work OK. Let me know if it needs more
tuning...

Select 5 cells in a column, enter this formula in the formula bar

=IF(MOD(B1:B5,1)=0,"",INDEX(A1:A5,MATCH(MOD(B1:B5, 1),1-MOD(B1:B5,1),-1)))

and press CTRL-shift-enter. It returns a #N/A for the 5th row because there
is a remainder, but no other remainder small enough to fit it.
It returns the number in column A corresponding to which one it "matches"
with. It's sketchy on the ones with the remainder of .5, so it will try to
match with itself unless there is a .5 further up in the column.


"James W." wrote in message
...
I apoligize for not being more specific...
I am using MOD ( a1, 1). I would like an if formula that would tell me
that I can combine these two values. These values represent pallets, so
just
simply stating "combine" will sufice.

For instance

if(mod(b1,1)=0,"",if(mod(b1 + **B:B**)<1,"Combine","") b:b
obviously b:b won't work becuase it is taking all of the values, I would
just like it to tell me that it can be combined with one other value
a b wishfull results
1 1.20 "combine" if it could say "combine with 'b4" that would
be
better
2 1.00
3 2.00
4 1.50 "combine" if it could say "combine with 'b1" that would
be
better
5 2.90


"Dave R." wrote:

The mod of the two numbers? Are you referring to the remainder when
these
two numbers are divded by or into eachother? If so I'm not sure how
you're
arriving at what is "combined" or not..

Have you tried the MOD function? If you have tried that and can see the
results it produces, an IF formula wrapped around the MOD part will give
you
"combine" or ""; e.g. =IF(your formula<1,"combine","")




"James W." wrote in message
...
I have an array of numbers and am trying to see if the Mod of two of
these
number is less than 1. I have a long list so making a nested if
function
would be my last option.

Example

a b wishfull results
1 1.20 combine
2 1.00
3 2.00
4 1.50 combine
5 2.90

Thanks in advance.