Formula help

[Repoort]

Ok I am not sure this will go where I want it to but I am going to try this. I am new to excel and can not figure out this formula, if it is possible. I am trying....

if k20=2 then "22,400"
if h23="" then "36,000"
if h23=8 then "38,000"
if h23=9 then "39,000"
if h23 >= 10 then "22,400 each"

By using &if I have been able to get it all to work except the k20=2 value I want. When it does equal 2 it places the 22,400 and the 36,000 from h23="". For reference when k20=2 there will not be anything in h23.

i am trying to put put this into j23 and am hoping for a formula as I am not familiar with the macro. Any help is appreciated

 

[dreid1011]

Welcome to the board.

Perhaps this:

=IF(K20=2,"22,400",IF(H23="","36,000",IF(H23=8,"38,000",IF(H23=9,"39,000","22,400 each"))))

This will result in 36,000 when both cells are blank, or K20 = anything other than 2, and H23 is blank. So, if a 0 is accidentally entered into K20 and H23 is blank, then it will return 36,000.

 

[Repoort]

Thank you so much for that formula. Is there anyway that it would stay 36000 if there were a number less that 8 entered in h23? Other than that it works great. If that is only issue then i will work with it?

 

[FDibbins]

I would do this with a small lookup table and 2 IF statements
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

2​

[/td][td][/td][td]

36000​

[/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

8​

[/td][td]

38000​

[/td][td][/td][td]

38000​

[/td][/tr]

[tr][td]

4​

[/td][td]

9​

[/td][td]

39000​

[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

100​

[/td][td]

22400​

[/td][td][/td][td][/td][/tr]
[/table]
D3=IF(K20=2,22400,IF(H23<8,36000,VLOOKUP(H23,$A$2:$B$5,2,1)))

Note that if you are referring t actual values, don't put the numbers inside "" as it makes them text

 

[Repoort]

Thanks for the help everyone I greatly appreciate it. I got it to work the way I wanted.

 

[FDibbins]

Happy to help