Excel Formula HelpHh

Faze11

Hi all thaught myself the basics of excel in order to create something used at work. Was looking for a bit of help with one formula though.

In one column (A) I will have a rising percentage each week. In the next column (B) I want to have a number that will change depending on what is entered in column A.

E.G. IF I have 60% in A I want B to say 0. If I have 70% in A i want B to say 200. If I have 80% in A I want B to say 400. I want this to happen automatically without me having to touch column B once formula is entered.

Hope I have explained that ok. Any help much appreciated.

Lu Tze

Faze11 wrote: »

Hi all thaught myself the basics of excel in order to create something used at work. Was looking for a bit of help with one formula though.

In one column (A) I will have a rising percentage each week. In the next column (B) I want to have a number that will change depending on what is entered in column A.

E.G. IF I have 60% in A I want B to say 0. If I have 70% in A i want B to say 200. If I have 80% in A I want B to say 400. I want this to happen automatically without me having to touch column B once formula is entered.

Hope I have explained that ok. Any help much appreciated.

A nested if statement will do it.

Formula for B1
=if(A1=0.6,0,if(A1=0.7,200,if(A1=0.8,400,"Out of Range")))

I put in 0.6 etc instead of 60% assuming the percentage was a format. Out of range will come up for any value in A whic is not 60,70 or 80%

Thoie

Lu Tze's way will work well. However, if there's a chance that the numbers in the B column are likely to change, there are some other methods you could use.

If A=70% will always and forever mean that B is 200, use Lu Tze's way. If it's likely that you'll need to change it in a few month's time let us know and we can offer alternatives.

Faze11

Thanks guys. I should h.ave said. I want it so if a = anywhere between 70-79% b will = 200. If a - 80-90 b=400 and so on. Would that change the formula or could i use a < symbol.

Many thanks once again

**Timbuk2**

Faze11 wrote: »

Thanks guys. I should h.ave said. I want it so if a = anywhere between 70-79% b will = 200. If a - 80-90 b=400 and so on. Would that change the formula or could i use a < symbol.

Many thanks once again

=if(A1<0.7,0,if(A1<0.8,200,if(A1<0.9,400,"Out of Range")))

Thoie

You can use the < symbol, but think about your edge cases. Is the number rounded up to whole numbers? What if the background number is 69.9999%? What do you want it to show then? If you're rounding up, that might look like 70%, but give a result of 0.
Play around with this one, and adjust the .8 and .9 as needed

=IF(A1<0.7,0,(IF(A1<0.8,200,(IF(A1<=0.9,400,"Error")))))

Faze11

Seems to be working perfect. Many thanks once again.

PrzemoF

my 2 cents: if you think about using excel for something more than simple math and basic conditional formulas start learning visual basic for excel, especially if you have any programing experience.

My answer (Alt-F11 to open vba in excel):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
            If Target.Value > 60 And Target.Value < 70 Then Target(1, 2) = 0
            If Target.Value > 70 And Target.Value < 80 Then Target(1, 2) = 200
            If Target.Value > 80 And Target.Value < 90 Then Target(1, 2) = 400
    End If
End Sub

I normally don't do vba for something as simple as that, but... if you have 12 ranges instead of just 3 the nesting conditional formula would be really long and awkward to maintain.