Function to display certain number in cell

[Parkerm34]

Hello I’m trying to put together a price sheet for window sales and the prices are size dependent ie a window that is 70 inches is $950 dollars but so is a window that is 60 but a window that is 71 inches is $ 1150there is a threshold for price difference. So basically i need a function that states if e8 is = 0 then print zero in g8 if e8 is >=1 but <70 print 950 in g8 so on and so forth. sorry if this is confusing . I am not an excel master. I tried this but it wont work correctly

=IF((E8)=0,0,IF(AND((E8)>1,(E8)<=70),950,IF(AND((E8)>70,(E8)<=89),50,1150))) this only prints 0 or 1150 but nothing else.

[MrShorty]

That looks like a lookup table + lookup function kind of problem (using the "approximate match" option in your chosen lookup function). Using VLOOKUP() (https://support.microsoft.com/en-us/...rs=en-us&ad=us ):

1) Create your lookup table:

Please Login or Register  to view this content.

2) Enter your lookup function =VLOOKUP(size,absolute_reference_to_lookup_table,2,TRUE)

Will something like that work for you?

[Sam Capricci]

I think based on what you wrote that this is a simplification of what you want.
=IF(E8=0,0,IF(E8<=70,950,IF(E8<=89,50,1150)))
You don't need so many parentheses and in a multiple IF statement they read left to right so the first thing that solves the problem - such as 48 in E8, then it will return 950, if it is above that it will return 50, otherwise 1150.

[Parkerm34]

This might work it would just need to always return 0 if 0 and always return 950 if between 1 and 70. Between 71 and 89 return 1150 and so on

[Parkerm34]

The 50 was a mistake. Basically if it is 0 it returns 0. If between 1 and 70 it prints 950. 71 to 89 it prints 1150 and so on.

[Sam Capricci]

not sure if you adjusted it or not, but basically if the 50 was a mistake then just change it to this.
=IF(E8=0,0,IF(E8<=70,950,IF(E8<=89,1150,"")))
though I'm not sure what you mean by "and so on" if we are supposed to keep going up from there using our guesses for each increment?
If you have more of them then as Mr Shorty noted in post #2, you might want to set up a table and use a vlookup for this.