I do my taxes with a spreadsheet. I want a formula to calculate 'applicable figure' (stupid name!) in IRS form 8962. Has anyone done this? I found a page in Google Groups that isn't good enough.

I decided it was hopeless, created a lookup table. I made a CSV version, good for 2018's taxes, as of 2018 December 18. You can find it at https://pastebin.com/4UZN0tJg . I use sc; if anyone wants the sc version, ask.

Been a few years since I have had to do logical formulas. Untested but here is an Excel example.

Similar for libreoffice except it uses ; instead of , I think.

L5 is your 8962 line five cell value.

Crap... After I paid more attention to the table I noticed that it isn't a simple first order equation... You might be able to graph the values and see what a higher order equation looks like and replace it with mine.

So I went off and decided there is no obvious math for the values in the table at and IRS 8962 Instructions. Accordingly I created the list of values with vim and sort:
(Start with 0 since any number below 133 = 0.0201, end at 300 since any number at or above [to 400] = 0.0956. [The PDF doesn't say whether it is possible to go above 400 and if so what to do.])
0 0.0201
133 0.0302
134 0.0308
135 0.0314
136 0.0320
137 0.0326
138 0.0332
139 0.0338
140 0.0344
141 0.0350
142 0.0355
143 0.0361
144 0.0367
145 0.0373
146 0.0379
147 0.0385
148 0.0391
149 0.0397
150 0.0403
151 0.0408
152 0.0412
153 0.0417
154 0.0421
155 0.0426
156 0.0431
157 0.0435
158 0.0440
159 0.0445
160 0.0449
161 0.0454
162 0.0458
163 0.0463
164 0.0468
165 0.0472
166 0.0477
167 0.0482
168 0.0486
169 0.0491
170 0.0495
171 0.0500
172 0.0505
173 0.0509
174 0.0514
175 0.0519
176 0.0523
177 0.0528
178 0.0532
179 0.0537
180 0.0542
181 0.0546
182 0.0551
183 0.0555
184 0.0560
185 0.0565
186 0.0569
187 0.0574
188 0.0579
189 0.0583
190 0.0588
191 0.0592
192 0.0597
193 0.0602
194 0.0606
195 0.0611
196 0.0616
197 0.0620
198 0.0625
199 0.0629
200 0.0634
201 0.0638
202 0.0641
203 0.0645
204 0.0648
205 0.0652
206 0.0655
207 0.0659
208 0.0662
209 0.0666
210 0.0669
211 0.0673
212 0.0676
213 0.0680
214 0.0683
215 0.0687
216 0.0690
217 0.0694
218 0.0697
219 0.0701
220 0.0704
221 0.0708
222 0.0711
223 0.0715
224 0.0718
225 0.0722
226 0.0726
227 0.0729
228 0.0733
229 0.0736
230 0.0740
231 0.0743
232 0.0747
233 0.0750
234 0.0754
235 0.0757
236 0.0761
237 0.0764
238 0.0768
239 0.0771
240 0.0775
241 0.0778
242 0.0782
243 0.0785
244 0.0789
245 0.0792
246 0.0796
247 0.0799
248 0.0803
249 0.0806
250 0.0810
251 0.0813
252 0.0816
253 0.0819
254 0.0822
255 0.0825
256 0.0828
257 0.0830
258 0.0833
259 0.0836
260 0.0839
261 0.0842
262 0.0845
263 0.0848
264 0.0851
265 0.0854
266 0.0857
267 0.0860
268 0.0863
269 0.0865
270 0.0868
271 0.0871
272 0.0874
273 0.0877
274 0.0880
275 0.0883
276 0.0886
277 0.0889
278 0.0892
279 0.0895
280 0.0898
281 0.0901
282 0.0903
283 0.0906
284 0.0909
285 0.0912
286 0.0915
287 0.0918
288 0.0921
289 0.0924
290 0.0927
291 0.0930
292 0.0933
293 0.0936
294 0.0938
295 0.0941
296 0.0944
297 0.0947
298 0.0950
299 0.0953
300 0.0956

I imported that into an Excel workbook splitting it into 2 columns (using text import with space as delimiter) in one sheet I called "Lookup". In that the actual values above are in columns B and C and rows 5 through 173.

In another sheet in same work book I input a field for line 5 of 8962 (in cell C5 of that other sheet) then in the column next to it placed the following formula:
=VLOOKUP(C5,Lookup!$B$5:Lookup!$C$173,2,TRUE)
For any value below 133 it will give the Applicable Figure for "0" as it is 0 to 132. For any value above 300 it will give the Applicable Figure for 300. One could modify the formula to give an error if the value is above 400 given that the instruction doesn't define that - I didn't.

This site has a tutorial for VLOOKUP (with a slight error - it shows the lookup range as one column but that gives a #REF if you do that - you have to tell it both columns in your lookup table).

By the time I got back to post this I see OP already figured out to do a lookup table and the first responder figured out it wasn't a simple formula. I figured I'd share just as another example.

It's worse than that: the increment isn't monotonic: from 160 to 161 it goes up by .0005, from 161 to 162 it goes up by .0004, from 162 to 163 it goes up by .0005. It's hopeless.

There is no applicable figure above 400 because there's no PTC if you make that much.

The table you have is what I put in my pastebin 'cause I didn't want to clutter up LQ.

I hate relying on external links for key data because the external link locations may have changed or gone defunct by the time someone sees the post. There is many a historical post I've gone to with bad links that made it useless because the important details weren't in it. Worse yet other places screen scrape to make their own posts and often don't even have the links when you find them.

One of my blog posts relates to a post I found about an error after time change and it was maddening because I appeared to find multiple hits related to the error in a web search but every hit link I opened turned out just to be a copy of the original post which didn't answer the question.

I wasn't trying to steal your thunder. I noted in my post that you'd resolved it and said what you did before I posted - I just felt like, having gone through the trouble, I should share the details including the formula and some discussion.

Yeah, those pastebin links have a limited life. Back when I had my own website I would have posted it there and alerted the search engines. I always thought I was the only person writing his/her own spreadsheet to do taxes (+my friend Rick), but if there are others, it's useful to have it in a permanent place.

To document what I did:

1) pdftexted i8962.pdf
2) edited out everything but the table's entries
3) grepped on 0.0 for applicable figures
4) grep -ved on 0.0 for income ratios
5) imported those into those into a spreadsheet; because that's sc for me, a short script that incremented the row and set that cell's value to each entry in the file, and allowed me to combine the 2 and have sc put them parallel.
6) then I merged it into Taxes2018.sc, used an @index to point to it.

I wasn't slanging you; it's the right thing to do.