need help on conditional if formulas

Occasional Contributor

Trying to insert formulas for spreadsheet below


 RATE 1st $100,000RATE next $400,000RATE next $2,000,000RATE next $2,500,000RATE next $2,500,000RATE over $7,500,000
11 Replies
What's your goal with this spreadsheet?


See the attached demo.

to calculate bond rates, for each column - need correct formula(s) to enter. Thanks
is there a way to enter the formula in each column, so i can add a "total" at the end?


Yes, if you wish:

I appreciate your help greatly. the key is items in row 1 as "next" for each column?
Shouldn't e5 be 18,750?
Shouldn't f5 be 17,500?
Shouldn't g5 be .069% of anything over 7.5 million?


0.075% of $2,500,000 is $1,875 and not $18,750.

0.070% of $2,500,000 is $1,750 and not $17,500.

The remaining amount over $7,500,000 is $500,000.

0.069% of $500,000 is $345.

Perhaps the percentages should have been 0.75%, 0.70% and 0.69%? The jump from 1.00% to 0.075% is very large...


Thanks much, i believe you are correct.
I'll try entering that way, and see if it works.
it all seems to work, except if A5 is 0, i get negative numbers in all columns, how can i fix that?
best response confirmed by rchelpforrc (Occasional Contributor)


See the attached version.

Hans, thanks much, you have solved my problem(s).