Greater than less than formula

[bvasquez551]

Hello! I am calculating hours a customer is using my facility. Billing schedule is as follows- 4 hours the rate is $1000, less than 4 hours is $300/h, 8 hours the rate is $2000. Over 8 hours it is $2000 plus $300 for every hour over. I have the formulas for all of them. I now need a formula stating-If the hours are greater than 4 but less than 8 the rate is $1000 plus $300/hour up to that 8 hours.
“Hours” begin in D2 and go to D8 if that helps.

 

[Micron]

Removed post; I mis-read some of it. Will have to review and see if I have anything to suggest.

 

[Micron]

Maybe =If(And(hrs>4,hrs<8),1000+300*hrs) in the cell containing the calculated result, where "hrs" is the cell address where you have calculated the hours. I suspect that is not in D2 to D8, but maybe it is. Posting data is usually more help than a description. Have to say though, that I thought the more your use/buy something the better the rate you get. That doesn't seem to be the case with your pricing

 3 hrs:           900 ($300.00/hr)
 4 hrs:          1000 ($250.00/hr)
 8 hrs:          2000 ($250.00/hr)
 9 hrs: 2000+300=2300 ($255.56/hr)
10 hrs: 2000+600=2600 ($260.00/hr)
11 hrs: 2000+900=2900 ($263.64/hr)

A potential problem with that is that there is no alternate if the IF is not true. I don't think there can be either since there are multiple equations so which one would you default to? I'd prefer a function call in the cell and let code figure it out.

 

[kevin9999]

Perhaps this?

Book1
	D	E
1	Hours	Cost
2	0.5	$150
3	3	$900
4	4	$1,000
5	6	$1,600
6	7	$1,900
7	7.75	$2,125
8	8	$2,000
9		$0
10	10	$2,600
11	12.75	$3,425
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=IFS(D2<4,D2*300,D2=4,1000,AND(D2>=4,D2<8),1000+((D2-4)*300),D2=8,2000,D2>8,2000+((D2-8)*300))

 

[Alex Blakenburg]

This might work.

=IF(D2<=8,INT(D2/4)*1000+MOD(D2,4)*300,2000+(D2-8)*300)

 

[kevin9999]

This might work.

=IF(D2<=8,INT(D2/4)*1000+MOD(D2,4)*300,2000+(D2-8)*300)

Love it - much more elegant