Easy way to make an IF

Mr_Blue1993

New Member
Joined
Jun 17, 2016
Messages
18
I'am kinda new at using If.

Is there a easy way to make a very long one?

What i want to do is....

if A1 is 200 then do A2 * 0.50 But if its 300 then do A2 * 0.75 but if it is 400 then do A2 * 0.6 and so on.... for about 30 ifs...
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Is there any regularity between the increments in the data (200, 300 etc) and the increments in the factor to be applied (*0.5,*0.75 etc).

Doesn't look so from your example, but if there is in the real world you could probably do this in a single formula.

If not, then you'll need to set up a separate table somewhere with the thresholds and factors to apply. Can work on that if needed, given answer to question above.
 
Last edited:
Upvote 0
You can do this using a LOOKUP formula:

=A2*LOOKUP(A2,{200,300,400,...},{0.5,0.75,0.6,...})
 
Upvote 0
if A1 is 200 then do A2 * 0.50 But if its 300 then do A2 * 0.75 but if it is 400 then do A2 * 0.6 and so on.... for about 30 ifs...[/QUOTE]

You need to use Vlookup:

ABCDEF
SeriesRandom NumResult
Vlookup

<tbody>
</tbody>
200
13600

<tbody>
</tbody>
=VLOOKUP(A2,$E$2:$F$6,2,1)*B22000.5
300
11986

<tbody>
</tbody>
7191.6

<tbody>
</tbody>
3000.6
400
7898

<tbody>
</tbody>
5528.6

<tbody>
</tbody>
4000.7
500
22172

<tbody>
</tbody>
17737.6

<tbody>
</tbody>
5000.8
600
23275

<tbody>
</tbody>
20947.5

<tbody>
</tbody>
6000.9
700
18651

<tbody>
</tbody>
18651

<tbody>
</tbody>
7001

<tbody>
</tbody>



I write on Cell C2 the formula:
=VLOOKUP(A2,$E$2:$F$20,2,1)*B2

$E$2:$F$20 is the table with data you want to insert,

let me know if its work...
 
Upvote 0
Omer_K

Thank you it works like a charm. I was hoping i could change data in the F Column but when i do the calculation does not change.

But you got me a very long way.
 
Upvote 0
The calculations should change if you change the values in the VLOOKUP lookup array.
 
Last edited:
Upvote 0
Without seeing the actual workbook, it's hard to tell, but there will be something amiss somewhere (but not with the formula, unless you have not entered it correctly - have you adjusted the arrays to suit your real data?).
 
Upvote 0
Omer_K

Thank you it works like a charm. I was hoping i could change data in the F Column but when i do the calculation does not change.

But you got me a very long way.


you're welcome :)

but like AliGW say the calculation should change
can you give me examples for some number its not work?
 
Upvote 0
Well i found it. now how to fix it...

The formula sees 8900 as 8450 and takes the calculation from 8450.

I changed the formula to make it working for the dutch Excel nothing more.

Code:
=VERT.ZOEKEN(A2;$E$2:$F$50;2;1)*B2
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top