How do I increase a value if...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

augustus - 14 Jul 2006 06:52 GMT

Hi,
How can I set up a sheet when the value is greater than 1.5 the product
will shown as 0.01 and every 1.5 it increases the 0.01.

for example, if a1=1.5 then a1=0.01, if a1=3 then a1=0.02, if a1=4.5
then a1=0.03 and so on.

Thx

Signature

augustus

Reply to this Message

VBA Noob - 14 Jul 2006 07:45 GMT

You could try a lookup.

Create a table in C1 to D3

=VLOOKUP(A1,$C$1:$D$3,2)

or a i statement

=IF(A5=1.5,0.01,"")&IF(A5=3,0.02,"")&IF(A5=4.5,0.03,"")

VBA Noob

Signature

VBA Noob

Reply to this Message

Dave Peterson - 14 Jul 2006 15:00 GMT

This kind of thing returns a string. If you really wanted to do something like
this, it might be better as:

=IF(A5=1.5,0.01,0)+IF(A5=3,0.02,0)+IF(A5=4.5,0.03,0)

> You could try a lookup.
>
[quoted text clipped - 13 lines]
> VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33833
> View this thread: http://www.excelforum.com/showthread.php?threadid=561364

Signature

Dave Peterson

Reply to this Message

Ian - 14 Jul 2006 07:47 GMT

Not sure about doing it in the same cell, but you could put this in B1

=INT(A1/1.5)*0.01

Signature

Ian
--

>
> Hi,
[quoted text clipped - 5 lines]
>
> Thx

Reply to this Message

Nick Hodge - 14 Jul 2006 07:54 GMT

Augustus

Your example has A1 referencing to itself in the formula. You can't do that
without creating a circular reference (generally bad). Here is a formula
you can put in say B1, referring to A1

=IF((A1/1.5)>=1,INT(A1/1.5)/100,0)

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

> Hi,
> How can I set up a sheet when the value is greater than 1.5 the product
[quoted text clipped - 4 lines]
>
> Thx

Reply to this Message

augustus - 14 Jul 2006 08:12 GMT

The formula worked very well.
As you can see I am totally a noob to excel. And I have another noob
problem needed to be solve. can someone please help me once more.
Quetion #2: per say, I have a table of data, and I would like to excel
to return the matching values from the table to a2.

Thx for the help everyone.
Highly Appreciated.

Signature

augustus

Reply to this Message