0

I am trying to calculate a price based on rates. If the number is $20,000 or below, there is a flat rate of $700. If the number is between 20,001.01 and $50,000, the rate is 3.5% of the number. The rates continue to lower as the numbers go up. I can get Google Sheets to populate the box with $700 if it is below $20,000 but I can't seem to make it do the multiplication for me. The cell just shows C4*.035

cell image

I want it to multiply the number shown in the C4 square by the percentage listed. Here is the code as it currently sits:

=if(AND(C4<=20000),"700",IF(AND(C4>=20000.01,C4<=50000),"C4*.035", IF(AND(C4>=50000.01,C4<=100000),"C4*.0325", IF(AND(C4>=100000.01),"C4*.03"))))

Note, I know nothing about coding so I apologize if it is sloppy or doesn't make sense. I tried to copy and format based on an example that was semi similar to mine.

DuDa
  • 3,718
  • 4
  • 16
  • 36
AUSTIRENO
  • 1
  • 2
    Enclosing `C4` within quotes means that you're no longer referring to the cell `C4`, but to the text "C4". Don't use quotes around cell references or numbers, otherwise you've made them text. In other words, remove all quotes from your formula. – BigBen Jan 13 '21 at 18:23
  • @BigBen That worked! You are awesome. Thanks so much. – AUSTIRENO Jan 13 '21 at 18:27

2 Answers2

0

try:

=IF(C4<=20000, 700, 
 IF(AND(C4>=20000.01, C4<=50000), C4*0.035, 
 IF(AND(C4>=50000.01, C4<=100000), C4*0.0325, 
 IF(AND(C4>=100000.01), C4*0.03))))
player0
  • 124,011
  • 12
  • 67
  • 124
0

As BigBen noticed in his comment - there's a mistake in your formula. You should not use " " around the formula if you don't want it to be read as a string.

Actually more clean solution is using IFS formula for this task.

    =ifs(C4<=20000,700,
C4<=50000,C4*0.035,
C4<=100000,C4*0.0325,
C4>100000,C4*0.03)
Krzysztof Dołęgowski
  • 2,583
  • 1
  • 5
  • 21