Can anyone help please?

V

vertigo

Hi All,

I have a very basic knowledge of excel so please forgive me if this is
actually ridiculously easy to do.

What i'm trying to do is this:

I have a basic addition formula in A1. What i am trying to do is have
another cell read A1 and if there is any amounts of 2000 or more in the
addition sum i want it to say "amount of 2000" or what ever the amount
is.

So if the sum was say: 100+200+300+400. It wouldn't do anything but if
the sum was 100+200+2564. It would say "Amount of 2564".

All i have been able to do so far is have this message come up if the
result of the actual sum is 2000 or over but i can't get it to just
read the individual amounts in the sum.The formula i have been using
is:

=IF((A1>=2000),"Amount of " &A1,"")

I hope this makes sense, if anyone can help i would greatly appreciate
it.

Thanks.
 
B

Bearacade

To be honest, I don't know how you could extract it without using
lengthy Visual Basic Module
 
E

Elkar

I'm not sure I completely understand what you're trying to do.

So, you want to look at the individual amounts in cell A1, not the actual
value of A1, to see if any are 2000 or greater? So, 600+600+600+600 would
not be displayed, since there are no individual values greater than 2000,
even though the total value is 2400? Is this correct?

If so, what is the reason for keeping all values in the same cell rather
than seperate cells? This makes the formula much more difficult, especially
since you don't seem to have the same number of values being added together.

What I'd recommend is placing your values in seperate cells, lets say A1
through A5. Then your formula could be

=IF(MAX(A1:A5)>=2000,"Amount of "&SUM(A1:A5),"")

If this won't work, please post back with more details.

HTH,
Elkar
 
V

vertigo

Elkar said:
I'm not sure I completely understand what you're trying to do.

So, you want to look at the individual amounts in cell A1, not the
actual
value of A1, to see if any are 2000 or greater? So, 600+600+600+600
would
not be displayed, since there are no individual values greater than
2000,
even though the total value is 2400? Is this correct?

If so, what is the reason for keeping all values in the same cell
rather
than seperate cells? This makes the formula much more difficult,
especially
since you don't seem to have the same number of values being added
together.

What I'd recommend is placing your values in seperate cells, lets say
A1
through A5. Then your formula could b

=IF(MAX(A1:A5)>=2000,"Amount of "&SUM(A1:A5),"")

If this won't work, please post back with more details.

HTH,
Elkar
Click to expand...

Sorry i should have explained myself better, yes Elkar i want it to
just show up individual amounts of 2000 and more, but not when i have a
sum of say 600+600+600+600 = 2400.

The reason they are all in the one cell is because the numbers which
will go into A1 varies each time.Sometimes it can be just two amounts
and sometimes it can be 10+ so it would be very messy having loads of
cells.
 
E

Elkar

Unfortunately, there is no easy way to do this then.

What if you had a second worksheet to store the numbers in? Say, column A
of Sheet2? This way you could enter as many numbers as you like, but your
main sheet would remain clean.

=IF(MAX('Sheet2'!A:A)>=2000,"Amount of "&SUM('Sheet2'!A:A),"")

Your other option would be to use VB Code, which I'm not real efficient at
writing. Perhaps someone in the Excel Programming forum could assist there.

HTH,
Elkar
 
K

kassie

I agree with Elkar, that you should use another sheet as input range for A1.
You can then check the individual cells in this range, to get your result.
something like =IF(MAX('Sheet2'!A:A)>=2000,"Amount of "&MAX('Sheet2'!A:A),"")
 
Top