1

I have an excel workbook with multiple sheets and I am using pandas to access it. The first sheet is the one that contains formulas that access the rest of the sheets and perform some calculations. When I access the first sheet and print the data frame, the columns that uses formulas are printed as Nan. How can I get the value instead of Nan?

import numpy as np 
import pandas as pd 

refitemPath = ".\\170310-test.xlsx"

xlsxFile = pd.ExcelFile(refitemPath)
df = pd.read_excel(xlsxFile, 'Result')
print(df)

Here is an example result enter image description here

and the actual values

enter image description here

paul
  • 121
  • 1
  • 10
  • Sorry, I can't get this to replicate. Can you possible provide a better example? I have a .xlsx with two sheets, one of which refers to another sheet with a formula, and it reads in values instead of NaNs just fine. Perhaps try specifying a dtype for the relevant column? https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html – Dylan Jan 02 '19 at 21:44
  • If it's not too much of a hassle, can you just create another spreadsheet and paste from the original file but only keep the value? – Xiaoyu Lu Jan 02 '19 at 21:47
  • @Dylan My formula is, =IF(ROUNDDOWN(VLOOKUP(J4,'Inventory'!$A:$Z,6)/3,0)-4>0,ROUNDDOWN(VLOOKUP(J4,'Inventory'!$A:$Z,6)/3,0)-4,0), not sure if this helps. I will try specifying a dtype. – paul Jan 02 '19 at 22:36
  • @XiaoyuLu I am trying to automate the entire process, I think creating a new spreadsheet will not help because I would still need the results from the formula – paul Jan 02 '19 at 22:38
  • @Dylan I try using dtype, and converters. Had no luck. – paul Jan 02 '19 at 22:52
  • https://stackoverflow.com/a/42180446/1675954 a regex solution – Rachel Gallen Jan 02 '19 at 23:08
  • https://stackoverflow.com/a/41730454/1675954 a possible alternative solution – Rachel Gallen Jan 02 '19 at 23:11
  • @RachelGallen The second link, I did as Colton T suggested, save the file first. That worked, it displayed the numbers. – paul Jan 02 '19 at 23:21
  • @paul brilliant. Success! Always good to have a happy ending :) – Rachel Gallen Jan 02 '19 at 23:24
  • @RachelGallen Thanks! – paul Jan 02 '19 at 23:35
  • Possible duplicate of [pandas read excel values not formulas](https://stackoverflow.com/questions/41722374/pandas-read-excel-values-not-formulas) – Aditya Jan 03 '19 at 06:14

0 Answers0